Archive of Mr Excel Message Board
Back to Excel VBA archive index
Back to archive home
Blank/Null CellsPosted by nessasmith on November 13, 2001 2:27 AM
I have recorded a macro using the find blank cell procedure (Goto > Special > Blank cell) to determine the final cell in a row. However I am recieving the message "No Cells Were Found". I need to be able to return the address of the final cell that holds no data. Can you search for a null cell? or make all cells that are null active?
|Check out our Excel VBA Resources
Re: Blank/Null CellsPosted by Damon Ostrander on November 13, 2001 8:52 AM
There is actually a much easier way to to this in VBA using the End method (equivalent in Excel to doing Ctrl-Arrow key). For example, if you want the row number of the final cell that holds data in column A on the active worksheet:
Dim RowNum As Long
RowNum = [a65536].End(xlUp).Row
On the other hand if you want the actual cell reference to that last cell:
Dim LastCell As Range
Set LastCell = [a65536].End(xlUp)
Both of these simply start in cell A65536 and search up (xlUp) until they find a cell containing data. And these functions, being built-in Excel object methods, are extremely fast.
Best wishes for many "happy endings."
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.