Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address of last piece of data on spreadsheet
Can someone please refresh me on the code to locate the
last occupied cell on a spread sheet, even if data on spreadsheet is non-contiguous. Specifically Row number & Column number of the last cell used. I am not referring to .Find("*") Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address of last piece of data on spreadsheet
Hi,
you could use Set myrange = ThisWorkbook.Sheets(1).UsedRange Then myrange.rows.count will give you row number and myrange.columns.count will give you column number Or you could use Set myrange = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCe ll) However the UsedRange property is less than perfect. For example, if you have a spreadsheet with 10 rows of data over 10 columns then UsedRange will show J10 correctly as the last cell. If you now clear the contents of 5 rows at the bottom and 5 columns at the right UsedRange will show E5 as the last cell. Right? Wrong!! The property will not reset until you save the wb or delete the cells that were used. The same is true of .SpecialCells (xlLastCell) incredibly. So the question you are asking; ie how to accurately find the last cell to contain data is ambiguous as how do you define the last cell? If there is data in IV1 and data in A65536 and no other data on a sheet, which one do you consider to be the 'last cell'? O -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address of last piece of data on spreadsheet
or just use the SpecialCells method:
Function LastUsedCell() As String LastUsedCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Address End Function Patrick Molloy Microsoft Excel MVP "Oliver Ferns via OfficeKB.com" wrote: Hi, you could use Set myrange = ThisWorkbook.Sheets(1).UsedRange Then myrange.rows.count will give you row number and myrange.columns.count will give you column number Or you could use Set myrange = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCe ll) However the UsedRange property is less than perfect. For example, if you have a spreadsheet with 10 rows of data over 10 columns then UsedRange will show J10 correctly as the last cell. If you now clear the contents of 5 rows at the bottom and 5 columns at the right UsedRange will show E5 as the last cell. Right? Wrong!! The property will not reset until you save the wb or delete the cells that were used. The same is true of .SpecialCells (xlLastCell) incredibly. So the question you are asking; ie how to accurately find the last cell to contain data is ambiguous as how do you define the last cell? If there is data in IV1 and data in A65536 and no other data on a sheet, which one do you consider to be the 'last cell'? O -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address of last piece of data on spreadsheet
A slow formula way for rows:
=SUMPRODUCT(MAX(ROW(1:2000)*(1:2000<""))) and this for columns: =SUMPRODUCT(MAX(COLUMN(1:256)*(1:2000<""))) The larger the range, the slower the calculation. In code: MsgBox [SUMPRODUCT(MAX(COLUMN(1:256)*(1:2000<"")))] _ & " columns" Regards Robert McCurdy "Chris W." wrote in message ... Can someone please refresh me on the code to locate the last occupied cell on a spread sheet, even if data on spreadsheet is non-contiguous. Specifically Row number & Column number of the last cell used. I am not referring to .Find("*") Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
take name from the spreadsheet cell and get address from outlook | Excel Discussion (Misc queries) | |||
Displaying what column a piece of data is in | Excel Worksheet Functions | |||
Extracting single piece of data | Excel Discussion (Misc queries) | |||
How do i get an excel spreadsheet to fit on one piece of paper | Excel Discussion (Misc queries) | |||
LINKING Address cells from an EXCEL spreadsheet to fill MapQuest Address Info | Excel Programming |