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]

Hi Fluff

Your code worked, as intended - thank you.

However, I need to modify it for a large set of data, so would prefer to check a couple of things, if I may, please?

Why is the number '2' used after the word 'autofilter? Is it because there are only 2 options ("Y" and "N")?

Why do we offset by 1 when deleting the entire row? I initially though that we were offseting by 1 because column B is 1 column away from column A. But I may be wrong.

I look forward to your response.

TIA

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

 

[Mr2017]

Hi Fluff

I think I've worked out the answer to my additional question above, but please correct me if I am wrong.

The "2" above appears because B is the second column in the range of data we're looking at.

And we have to offset by 1 because we dont want to delete anything in the row with the autofilter.

Is that correct?

TIA

 

[Fluff]

Yes, that is correct.

 

[Mr2017]

Ok, excellent!!

Thank you for the solution!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Mr2017]

Thanks for your help!

 

[Fluff]

 

[Mr2017]

Hi Fluff

Thanks for the solution above - it was very helpful!

I have come across a new instance where I need to delete rows that contain a certain word.

Do you know if there is a way to modify this query so that it deletes rows which have the text 'Global'?

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

 

[Fluff]

Just change the N to Global.

 

[Mr2017]

Oh, cool!!

Thanks!!

I didn’t know it would be that simple!

I thought the “N” was filtering by that criteria, only! So I thought it was checking that the cell was equal to “N” rather than checking if it contained “N”

Thanks again! The help is much appreciated!