VBA code applies AutoFilter to the wrong row

Question

I expect that it is something simple that I am missing. I am expecting the following line of code to apply an autofilter to row 9.

            Ws.Range("A9").AutoFilter

On some worksheets, it applies it to row 4. What am I missing?

Answer 1

Hi,

If you leave row 8 empty then this works fine

ws.Range("A9").AutoFilter

If you don't want to leave A8 (or is it B?) then you can do this. Change the A's to B's if required

Dim ws As Worksheet

Dim LastRow As Long

Set ws = Sheets("Sheet1")

LastRow = ws.Range("A9").End(xlDown).Row

ws.Range("$A$9:A" & LastRow).AutoFilter

Answer 2

Actually, column A has nothing in it, but it is hidden. Column B has labels in rows 4 to 8, data headers in 9, and data in 10 to 68. The number of rows with data varies from month to month, but in column B there are no blanks.

Unfortunately there is also data in rows 71 to 180 which is not part of the table that needs to be autofiltered, so finding the last used row won't work for me.

If I could select B9, do the equivalent of END DOWN and then go back up one row, that would get me the last row of the table. It still doesn't put the filters in row 9.

Would it help if I always had a blank row 8? I could modify the worksheets to consistently do that.

Answer 3

Hi,

I suspect that in the sheets where this goes wrong you have data in A4 thru A8 as well as in A9 down where you want the filter to go.

Excel will do exactly the same if you select A9 and then click filter. The way around it is to specify the range like this

Dim ws As Worksheet

Dim LastRow As Long

Set ws = Sheets("Sheet1")

LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

ws.Range("$A$9:A" & LastRow).AutoFilter