VBA: Filter dataset based on listed values column A Sheet2

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,360
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This code works fine to remove rows from a dataset that contain the values stored in the Array.

In lieu of the Array, can I use a list of data in column A on Sheet2?

With the Array, I will have to change it as soon as another variable is required to be deleted the next time the dataset is refreshed.

Code:
Sub DelRows1()
    Application.ScreenUpdating = False
    With ActiveSheet.Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=Array("AFDZ", "ANA", "FMS", "N/A", "SOF","BCXX), Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi FryGirl,

Try this:

Code:
Option Explicit
Sub DelRows2()

    Dim lngArrayCount As Long
    Dim rngMyCell As Range
    Dim varMyArray() As Variant
    
    Application.ScreenUpdating = False
    
    'Create an array of names to be deleted from cell A2 to A[last row] in Sheet2. Change to suit.
    For Each rngMyCell In Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
        If Len(rngMyCell) > 0 Then
            lngArrayCount = lngArrayCount + 1
            ReDim Preserve varMyArray(1 To lngArrayCount)
            varMyArray(lngArrayCount) = rngMyCell
        End If
    Next rngMyCell
    
    'Delete all rows from Sheet1 cell B2 to B[last row] whose text is in the 'varMyArray' array. Change to suit.
    With Sheets("Sheet1").Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=Array(varMyArray), Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With

    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 
Upvote 0
Another option
Code:
Sub DelRows1()
   Dim Ary As Variant
   
   With Sheets("sheet2")
      Ary = Application.Transpose(.Range("a2", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
    Application.ScreenUpdating = False
    With ActiveSheet.Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter 1, Ary, xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Hi Fluff,

Can I ask you a question - what's the syntax for filtering the data set where the items do not agree to what's in the array i.e. what's wrong with this...

Code:
.AutoFilter Field:=1, Criteria1:="=<>" & Array(50566, 50567, 50569, 50571, 50573, 50968, 51405), Operator:=xlFilterValues

...the code just keeps returning a "Run-time error 13 – Type mismatch" error message.

Any advice would be appreciated.

Regards,

Robert
 
Upvote 0
You can only filter on 2 criteria unless you are doing an exact match.
 
Upvote 0
You're welcome, you could have a look at Advanced filters, they can do that sort of thing.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top