Deleting all options in a filter, except one, using VBA

[Mr2017]

Hi

I've got a list 4 statuses in column U of a Sheet (Active, Inactive, Pending Active and Pending Inactive).

I thought this code would work, but it deletes ALL 4 options, instead of leaving the Active rows.

Does anyone know how to modify this so that it deletes everything BUT the Active rows, please?

Also, do I need to repeat the code for every option?

TIA

    On Error Resume Next
    With ActiveSheet
        .Range("U1:U").AutoFilter 20, "Inactive"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Automodefilter = False
    End With
    On Error GoTo 0
    
        On Error Resume Next
    With ActiveSheet
        .Range("U1:U").AutoFilter 20, "Pending Inactive"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Automodefilter = False
    End With
    On Error GoTo 0
    
        On Error Resume Next
    With ActiveSheet
        .Range("U1:U").AutoFilter 20, "Pending Active"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Automodefilter = False
    End With
    On Error GoTo 0
    
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

Status

Inactive

Active

Pending Inactive

Pending Active

 

[DanteAmor]

Try this:

Sub Deleting_Options()
  With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1:U" & .Range("U" & Rows.Count).End(3).Row).AutoFilter 21, Array("Inactive", "Pending Active", "Pending Inactive"), xlFilterValues
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
End Sub

 

[Mr2017]

Hi Dante

Thanks for the prompt response.

I tried that, but then got a "Run time error: 1004. Autofilter method of range of class failed."

Do you know why that may have happened?

And thanks for the other solution that you posted recently, on a different question - it was helpful!!

 

[Mr2017]

Also, the number of rows of data will vary....

I don't know if that will make a difference to how the code needs to be written?

 

[DanteAmor]

I tried that, but then got a "Run time error: 1004. Autofilter method of range of class failed."
Do you know why that may have happened?

It works for me, maybe the excel version.

Here another approach

Sub Delete_Option_2()
  Dim lr As Long, i As Long, a, r As Range
  Application.ScreenUpdating = False
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  lr = Range("U" & Rows.Count).End(3).Row
  Set r = Range("A" & lr + 1)
  a = Range("U2:U" & lr)
  For i = 1 To UBound(a)
    Select Case a(i, 1)
      Case "Inactive", "Pending Active", "Pending Inactive"
        Set r = Union(r, Range("A" & i + 1))
    End Select
  Next i
  r.EntireRow.Delete
  Application.ScreenUpdating = True
End Sub

 

[Mr2017]

Ok, I'll try the alternative a bit later, thanks.

 

[Mr2017]

Ok - yes!

That latest code worked!

Thank you very much!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Mr2017]

Thanks for your help! It was much appreciated!

 

[Jette3173]

Try this:

Sub Deleting_Options()
  With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1:U" & .Range("U" & Rows.Count).[S][COLOR=rgb(209, 72, 65)]End(3)[/COLOR][/S].Row).AutoFilter 21, Array("Inactive", "Pending Active", "Pending Inactive"), xlFilterValues
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    .ShowAllData
  End With
End Sub

For anyone searching like mad for a fix for this. This works with one small change!
Remove the End(3).