Finding the Last empty Row within a table using .rows.count confusion

Question

I have a table with a List of data in it. I have chosen ws.Cells(ws.Rows.count,"A").End(xlUp).row to find the first cell that is empty within column A.

The problem is that the code outcome is stuck on the length of the table despite having empty cells before the end table length. Clearing all cell formats and content within the table has not worked.

Does this particular code recognize the table element as cell occupancy? Or am I missing a portion of code? Thanks a bunch

see here for other methods that will work: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — Scott Craner, Aug 15 '16 at 20:41
@ScottCraner - OP is after first empty cell, not last non-empty cell. — YowE3K, Aug 15 '16 at 20:58

score 1 · Accepted Answer · answered Aug 15 '16 at 20:57

1

ws.Cells(ws.Rows.count,"A").End(xlUp).row will find the last non-empty cell in column A.

To find the first empty cell in column A (assuming cell A1 itself is not empty) you could use:

ws.Cells(1,"A").End(xlDown).row + 1

answered Aug 15 '16 at 20:57

YowE3K

23,852
7
26
40

The alternative worked great! I wonder why my approach recognized table element as cell occupancy. – TJYen Aug 15 '16 at 21:14
You could always just press the `Delete` key on the cell that `ws.Cells(ws.Rows.count,"A").End(xlUp).row` is reporting, but I posted my answer on the assumption that you had data that you wanted in that cell (e.g. a totals record, or perhaps the end of a table other than the one you were trying to find the end of, etc, etc). – YowE3K Aug 15 '16 at 21:30

Finding the Last empty Row within a table using .rows.count confusion

1 Answers1