Search code examples
excelvba

Finding last row containing data not working - possible problem with table formatting

I've got a sheet called MachineData and I've got the following code

Worksheets("MachineData").Activate

'Last row with data on column AD (30)
lrow3 = Cells(Rows.Count, 30).End(xlUp).Row

The problem, is that lrow3 returns 2166 when it should return 1 because that's the last row on that column that contains data.

This column was added next to a table containing data that I extracted from a database. I believe that because the data was extracted from the database then this table has some weird formatting that makes this function not work.

My question is, how can I change the formatting of my table so this doesn't happen? or is there a way to change my code so this doesn't happen within this table?

Here is a picture of what my column looks like, anything below "File Name" is empty. I just get those green and white cell colours as this is part of the larger table.

enter image description here

Solution

  • For your particular context, i.e. with blank cells at the end of your table

    lrow3 = Cells(Rows.Count, 30).End(xlUp).End(xlUp).Row