Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell from one worksheet to another worksheet in exel | Excel Discussion (Misc queries) | |||
populate cell on worksheet 2 if text is red on worksheet 1 | Excel Worksheet Functions | |||
Variable worksheet name (tab) linked to cell in same worksheet | Excel Worksheet Functions | |||
How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions | |||
selecting cell range in other worksheet without switching to worksheet | Excel Programming |