Find Last Row for Table Size

[kersplash]

Hi,

I'm trying to automate through VBA the creation of a table.

The problem is I have blank rows in the raw data. I can't even apply a filter and delete the blanks because the filter stops when it hits the blank row and doesn't include the rest of the data.

At the moment I have to manually create a table with the dimensions of A1 through to the last column and row of the entire range, I can then filter out the offending blanks. But I would like to cut out the manual table creation.

Thanks

[xlnitwit]

Hi,

Is there one column that should always have data for a valid row?

[kersplash]

No the entire row is blank.

[xlnitwit]

I appreciate that. What I mean is, for a row that has some data, is there a specific column that would always be populated?

[kersplash]

I appreciate that. What I mean is, for a row that has some data, is there a specific column that would always be populated?

Yes, there are columns that always have data. In one particular column the data is always the same text. If I try and filter on this column though the filter stops working one it hits the blank line.

[xlnitwit]

In that case you can use that column to specify the last row of actual data, and either create the table from the relevant range, or use SpecialCells to delete the blank rows first. Absent any real information as to the layout of your worksheet, that is about as specific as I can be.

[kersplash]

I've attached some demo data. I want the table to go to the last row with data in it.

[tax112]

I've attached some demo data. I want the table to go to the last row with data in it.

Try:

Sub Test()
    Dim LR As Long
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox LR
End Sub  

[kersplash]

Cool thanks.