Excel Blank/Null Cells

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?

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

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.

Archive of Mr Excel Message Board

Blank/Null Cells

Re: Blank/Null Cells

Check out our Excel VBA Resources