Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

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

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

    Default Re: VBA Filter Issue

    I can change the formula on the front sheet for the above to work just wondering can I do it on the filter side

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,151
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: VBA Filter Issue

    OK, how about
    Code:
    Sub Del_Rows()
       Dim Countries(1 To 27) As Variant
       Dim Abc As Worksheet
       Dim cl As Range
       Dim i As Long
       
       Set Abc = ThisWorkbook.Sheets("123")
       
       For Each cl In Sheet1.Range("A2:A27")
          If Not cl.Offset(, 2).Value = "Y" Then
             i = i + 1
             Countries(i) = cl.Value
          End If
       Next cl
       
       
       Application.ScreenUpdating = False
       With Abc.UsedRange
          .AutoFilter 3, Countries, xlFilterValues
          .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 2003 & 2013 on Win 7

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

    Default Re: VBA Filter Issue

    That worked perfectly once I added in

    ReDim Countries(1 To 26 - Application.CountBlank(Sheet1.Range("A2:A27")))

    Thanks for much for the help.

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

    Default Re: VBA Filter Issue

    Only trouble I seem to have now is the below seems to no long apply:

    .AutoFilter Field:=6, Criteria1:=">=50"

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,151
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: VBA Filter Issue

    Not sure why, as it works for me. Are you sure that the numbers are real numbers rather than text?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

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

    Default Re: VBA Filter Issue

    Quote Originally Posted by Fluff View Post
    Not sure why, as it works for me. Are you sure that the numbers are real numbers rather than text?
    I have the below:

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

    if I put it like this none of the filtering works:

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

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

    Default Re: VBA Filter Issue

    Yeah those cells are set to general

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

    Default Re: VBA Filter Issue

    I think you may right as it will only let me filter as text. the problem I face is that in the column where I want filter greater than or equal to 50 thereis also values N.A. which I need to remain also.

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,151
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: VBA Filter Issue

    If you remove this line
    Code:
    .AutoFilter 3, Countries, xlFilterValues
    Does it filter correctly for col 6?
    Also when posting code please use code tags, the # icon in the reply window
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

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

    Default Re: VBA Filter Issue

    How I found to get it going was to do the filter for >= 50 in a separate sub and call them together in another sub so they work off a single button. It seems to do with both filters and then its doesn't recognize them. I tried commenting out that line and it didn't help I'm afraid.

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
  •