VBA code to select a value in a filter then delete everything with that criteria

[Mr2017]

Hi

I'd like to write some code that will apply a filter to row 1 in a sheet, then filter for a condition in the autofilter then delete everything that turns up when that condition is applied.

In the simple example below, I have the title 'Numbers' in cell A1, then the numbers 1, 2, 3, and 4 in cells A2, A3, A4 and A5, respectively.

In cell B1, I have the title 'Y or N' (short for 'Yes' or 'No') then cells B2: B5 have the values Y, Y, N, and N, respectively.

I'd like to modify the code below so that it works on a dynamic rather than fixed range.

Does anyone know how to do this, please?

Sub filter()
'
' filter Macro
'

'
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=2, Criteria1:="N"
    Rows("4:5").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$B$3").AutoFilter Field:=2
End Sub

Numbers	Y or N?
1​	Y
2​	Y
3​	N
4​	N

 

[Fluff]

So I thought it was checking that the cell was equal to “N”

It is.