Results 1 to 9 of 9

Thread: VBA: Filter dataset based on listed values column A Sheet2
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Filter dataset based on listed values column A Sheet2

    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

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    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

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    Thank you. Both of these work great.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    You can only filter on 2 criteria unless you are doing an exact match.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    That's a shame. Thanks anyway.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: Filter dataset based on listed values column A Sheet2

    You're welcome, you could have a look at Advanced filters, they can do that sort of thing.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •