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?

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."

Damon