Find current blank cell (97)

Topic

New Reply

I am new to EXCEL vba and am trying to locate the next blank cell in a column using vba code. Once the next the blank cell is located, I am copying data, from another work sheet in the same file, across many columns in that row (i.e., columns A thru K). For example if A10, is the next blank cell in column A, the macro needs to make this the active cell. My present routine identifies the last row with data (i.e., A9 in my simple example) and keeps copying different data over the same cells (i.e., over writing data and the table never grows beyond row 9). Sorry for this simple question, but after an hour of work, I have given up. THANKS for your patience with those just starting the learning, er, struggling process..

Reply | Quote

Replies

If you want to find the first blank cell below A1, you can use:

Dim oCell As Range
Set oCell = Range(“A1”).End(xlDown).Offset(1, 0)

End(xlDown) is the code equivalent of hitting the End key followed by the Arrow Down key. Offset(1, 0) then returns the cell 1 row down (and 0 columnd to the right, i.e. in the same column.) In most cases, you don’t need to select a cell or range to manipulate its contents. Methods like Copy and Paste work on ranges, and not selecting a range actually speeds up execution. But if you do need to select the cell, add

oCell.Select

If you want to find the blank cell below the last filled cell in column A, use

Dim oCell As Range
Set oCell = Range(“A65535”).End(xlUp).Offset(1,0)

If for instance A1:A9 and A13:A18 are filled, this will return a reference to cell A19, while the first method would return a reference to cell A10.

Reply | Quote

Do you want the next blank cell in the column, or the cell after the last used cell in the column? Those will be different if there are any empty cells before the last used cell. If you want the cell after the last used cell in column A, then the following will give that:

    Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1,0)

BTW, it is almost never necessary to select a cell in VBA to do something with that cell, and it is always much faster and better not to if you don’t have to.

Reply | Quote