Code for last row of data before autofilter - if necessary

[bigpat]

I created a macro by recording it, where I want to set an autofilter on a table that starts at row 11 and then select multiple entries.

This is the relevant part of that code and it includes cell T4806 because that is CURRENTLY the last row of data.

ActiveSheet.Range("$B$11:$T$4806").AutoFilter Field:=7, Criteria1:=Array( _
        "Breast Surgery", "Colorectal Surgery", "ENT", "General Surgery", "Oral Surgery", _
        "Paediatric Surgery", "Plastic Surgery", "Trauma And Orthopaedics", "Urology"), _
        Operator:=xlFilterValues

But the file is updated each week so next week there may be more or less data. It will always be row T though.

I searched elsewhere and came up with this next line to identify the last row of data and I know it does work. I put that line before the code above.

LR = Worksheets("MyDataSheet").Cells(Rows.Count, 2).End(xlUp).Row

But how should I change the code to incorporate LR? I tried various combinations like "$B$11:$T$"&LR&" but none work.

 

[Fluff]

As long as you don't have any blank rows in the data you can use

ActiveSheet.Range("B11:T11").AutoFilter

Alternatively, you can use

ActiveSheet.Range("B11:T"&LR).AutoFilter

 

[bigpat]

Thank you! Do you mean that the first version is if you don't have blank rows and the second is if you do have some blanks? I tried the second version and it works fine on copies of the file with more or fewer rows but I made a copy with some blanks and it only sets the autofilter as far as the row before the first blank.

But that might be be because LR is no longer returning the right value.

I don't think there wil be any blank rows, but I'm helping my son figure this out as a favour, so it's not my data and I'm not 100% sure.

 

[Fluff]

If you use

lr = Cells.find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row

This will find the last used row regardless of columns.

 

[MARK858]

I tried the second version and it works fine on copies of the file with more or fewer rows but I made a copy with some blanks and it only sets the autofilter as far as the row before the first blank....
But that might be be because LR is no longer returning the right value.

What happens if you change the LR line to

LR = Columns("B:T").Find("*", , xlValues, , xlByRows, xlPrevious).Row

Just a bit more restricted range than the code Fluff has posted in case there is data outside of the columns referenced in the filter.