VBA: Filter dataset based on listed values column A Sheet2

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,170
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,610
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
42,865
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,170
Office Version
2016
Platform
Windows
Thank you. Both of these work great.
 

Fluff

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

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,610
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
42,865
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
42,865
Office Version
365
Platform
Windows
You're welcome, you could have a look at Advanced filters, they can do that sort of thing.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,713
Messages
5,488,468
Members
407,639
Latest member
Carlos Ottoni

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top