VBA: Filter dataset based on listed values column A Sheet2

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,127
Office Version
2016
Platform
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
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,476
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,717
Office Version
365
Platform
Windows
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
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,127
Office Version
2016
Platform
Windows
Thank you. Both of these work great.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,717
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,476
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,717
Office Version
365
Platform
Windows
You can only filter on 2 criteria unless you are doing an exact match.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,717
Office Version
365
Platform
Windows
You're welcome, you could have a look at Advanced filters, they can do that sort of thing.
 

Forum statistics

Threads
1,085,423
Messages
5,383,586
Members
401,838
Latest member
mydog

Some videos you may like

This Week's Hot Topics

Top