VBA Filter Issue
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: VBA Filter Issue
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Filter Issue

    Hi

    I'm trying to filter using the below:

    Sub Del_Rows()


    Dim Countries As String
    Set wk = ThisWorkbook
    Set ABC = wk.Sheets("123")


    Set Countries = Sheet1.Range?????


    Application.ScreenUpdating = False
    With ABC.UsedRange
    .AutoFilter Field:=3, Criteria1:=Countries
    .AutoFilter Field:=6, Criteria1:=">=50"
    .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
    .AutoFilter
    End With
    Application.ScreenUpdating = True
    End Sub


    For field 3 I want to filter by a list of country codes which I have on my front sheet where the user has marked a y in the cell adjacent to each.

    any ideas?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter Issue

    Whereabouts in the sheet are the list of countries?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter Issue

    Quote Originally Posted by Fluff View Post
    Whereabouts in the sheet are the list of countries?
    I have them listed on sheet1

    Country Filter
    Argentina Y
    Brazil Y
    Mexico

    Thanks

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter Issue

    Whereabouts on the sheet?
    There are 16,384 columns by 1,048,576 rows where that data could be stored.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Sep 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter Issue

    Quote Originally Posted by Fluff View Post
    Whereabouts on the sheet?
    There are 16,384 columns by 1,048,576 rows where that data could be stored.
    Apologies:

    A B C D
    CE Country / Market Apply Filter CE
    ARG Argentina Y ARG
    AUS Australia
    BRA Brazil
    CAN Canada
    CHL Chile
    CHN
    COL Colombia
    HKG Hong Kong
    IDN Indonesia
    IND India
    JPN Japan
    KOR Korea
    MEX Mexico
    MYS
    NZL Nea Zealand
    PER Peru
    PHL Phillippines
    SGP Singapore
    THA Thailand
    TWN Taiwan
    USA USA
    VEN Venezuela
    XHK
    XHZ
    XSH
    XSZ


    So the codes are located from D2:D27.

    Thanks

    Luke

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter Issue

    How about
    Code:
    Sub Del_Rows()
       Dim Countries() As Variant
       Dim Abc As Worksheet
       Dim cl As Range
       
       Set Abc = ThisWorkbook.Sheets("123")
       
       ReDim Countries(1 To 26 - Application.CountBlank(Sheet1.Range("D2:D27")))
       For Each cl In Sheet1.Range("D2:D27")
          If Not cl.Value = "" Then
             i = i + 1
             Countries(i) = cl.Value
          End If
       Next cl
       
       
       Application.ScreenUpdating = False
       With Abc.UsedRange
          .AutoFilter Field:=3, Criteria1:=Countries
          .AutoFilter Field:=6, Criteria1:=">=50"
          .Offset(1).SpecialCells(xlVisible).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

  7. #7
    New Member
    Join Date
    Sep 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter Issue

    Thanks for that I tried it but it still doesn't filter the Countries, I can see it does pull in them from the in the loop but it doesn't seem to apply that as the filter.

    I have option explicit on so I set i as long.

    Thanks fo the help

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter Issue

    Missed a bit
    Code:
    With Abc.UsedRange
       .AutoFilter Field:=3, Criteria1:=Countries, Operator:=xlFilterValues
       .AutoFilter Field:=6, Criteria1:=">=50"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Sep 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter Issue

    Quote Originally Posted by Fluff View Post
    Missed a bit
    Code:
    With Abc.UsedRange
       .AutoFilter Field:=3, Criteria1:=Countries, Operator:=xlFilterValues
       .AutoFilter Field:=6, Criteria1:=">=50"
    Any ideas why that still wont work?

  10. #10
    New Member
    Join Date
    Sep 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter Issue

    i got it to work however it seems to be doing the opposite to what I want:

    .AutoFilter Field:=3, Criteria1:=Countries, Operator:=xlFilterValues
    .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
    .AutoFilter Field:=6, Criteria1:=">=50"
    .Offset(1).SpecialCells(xlVisible).EntireRow.Delete

    So its deleting the rows where field 3 equals the range in sheet 1 but I want it to delete the rows whereby field 3 does not equal Countries

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
  •