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

 

[Mr2017]

Also, if the rows with an 'N' have already been deleted, the I'd want to just like the code to 'Exit Sub'

Please let me know if I need to clarify anything else.

TIA

 

[tico_ocit]

Why you're using autofilter? Can that be solved with a loop? If the cell is "N" then delete entire row?

 

[Fluff]

How about

Sub Mr()
   With ActiveSheet
      .Range("A1:B1").AutoFilter 2, "N"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

This will filter all rows, as long as there are no blank rows within the data.

 

[Mr2017]

Great!!

Thanks @Fluff !

That worked!!

Thanks again!

 

[Mr2017]

@tico_ocit

I prefer to avoid using loops, as there was an occasion when I had thousands of rows to loop through, and the loop took forever!

So I always prefer to steer clear of loops.

But Fluff's code has done exactly what I'm trying to do!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Mr2017]

No problem.

Thank you.

 

[tico_ocit]

@tico_ocit

I prefer to avoid using loops, as there was an occasion when I had thousands of rows to loop through, and the loop took forever!

So I always prefer to steer clear of loops.

But Fluff's code has done exactly what I'm trying to do!

I thought it won't be easy to work with auto filter, I was wrong. I'm glad @Fluff come with a simple solution.

 

[Mr2017]

No problem.