LAddress of last cell in worksheet
I need to know the address of the last cell (non-empty) on my worksheet. I
see that .Rows.Count and .Columns.Count seem to show only the max size. "Last" is defined as a non-empty cell with the largest row and column address. thanks |
LAddress of last cell in worksheet
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address
will give you the last cell in the UsedRange although this may be empty if you have deleted the contents of some cells. Assuming you have headings in row 1 then: Dim eRow As Long Dim eCol As Integer eCol = Cells(1, Columns.Count).End(xlToLeft).Column eRow = Cells(Rows.Count, eCol).End(xlUp).Row MsgBox Cells(eRow, eCol).Address will give you the address of the last non-empty cell in the last column with a heading. Hope this helps Rowan Jim wrote: I need to know the address of the last cell (non-empty) on my worksheet. I see that .Rows.Count and .Columns.Count seem to show only the max size. "Last" is defined as a non-empty cell with the largest row and column address. thanks |
LAddress of last cell in worksheet
Hi Jim.
To find the last populated row and the last populated column, try the following functions: '================= Public Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function '----------------- Function Lastcol(sh As Worksheet) On Error Resume Next Lastcol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function '<<================= Whether the cell with the largest column value or the cell with the largest row value should be deemed as the last cell is for you to decide. --- Regards, Norman "Jim" wrote in message ... I need to know the address of the last cell (non-empty) on my worksheet. I see that .Rows.Count and .Columns.Count seem to show only the max size. "Last" is defined as a non-empty cell with the largest row and column address. thanks |
LAddress of last cell in worksheet
Thank you for ur fast and detailed reply
"Jim" wrote in message ... I need to know the address of the last cell (non-empty) on my worksheet. I see that .Rows.Count and .Columns.Count seem to show only the max size. "Last" is defined as a non-empty cell with the largest row and column address. thanks |
All times are GMT +1. The time now is 04:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com