Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

Blank/Null Cells

Posted 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 Cells

Posted by Damon Ostrander on November 13, 2001 8:52 AM
Hi 'nessaSmith,

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
All contents © 1998-2004
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.