• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

When Excel VBA uses AutoFilter, extra rows are hidden after filter

I've got VBA code that filters a portion of data (on sheet tab "Equipment Worksheet") after selections are made on a different sheet ("SW Worksheet, cells H251:H254). Everything works great EXCEPT that the filtering, which is supposed to end at row 164 on "Equipment Worksheet", actually hides everything through row 192, and I can't figure out how to only make it hide through row 164.

The file is attached: a selection has already been made on the "SW Worksheet;" to run the code, click the gray button at cell D68 on "Equipment Worksheet."

I appreciate any ideas!
Sub PickCoffee()
    Application.ScreenUpdating = False
    Dim br As String                'Brew Prep Area
    Dim barLen As String            'Bar Length
    Dim vol As String               'Volume
    Dim s As String                 'sink
    Sheets("SW Worksheet").Select
    'set barLen
    If IsEmpty(Range("H252")) Then
        MsgBox "Please select coffee bar length."
        Exit Sub
        barLen = Range("H252").Value
    End If
    'set s
    If IsEmpty(Range("H254")) Then
        s = "x"                     'no sink criteria will be used in filter
    ElseIf Range("H254").Value = "None" Then
        s = ""
        s = Range("H254").Value
    End If
    'set br
    If IsEmpty(Range("H251")) Then
        br = "x"                    'no brew prep criteria will be used in filter
    ElseIf Range("H251") = "back wall" Then
        br = ""
        br = "Y"
    End If
    'set vol
    If IsEmpty(Range("H253")) Then
        vol = "x"
        Select Case Range("H253").Value
            Case "Low"
                vol = "LV"
            Case "Med/High"
                vol = "MV/HV"
            Case "High"
                vol = "HV"
            Case "Extremely High"
                vol = "EV"
            Case Else
                MsgBox "Vol is " & Range("H253").Value
        End Select
    End If
    'If AutoFilter already on and filtered, then unfilter
    Worksheets("Equipment Worksheet").Activate
    If ActiveSheet.FilterMode Then
    End If
    'now filter data with given data
    'bar length
    Selection.AutoFilter Field:=7, Criteria1:=barLen
    'brew prep area
    If br = "x" Then
        Selection.AutoFilter Field:=12
        'ActiveSheet.Range("$G$69:$R$164").AutoFilter Field:=12, Criteria1:=br
        Selection.AutoFilter Field:=12, Criteria1:=br
    End If
    If vol = "x" Then
        Selection.AutoFilter Field:=8
        Selection.AutoFilter Field:=8, Criteria1:=vol
    End If
    If s = "x" Then
        Selection.AutoFilter Field:=9
        Selection.AutoFilter Field:=9, Criteria1:=s
    End If
    Application.ScreenUpdating = True
    MsgBox "Based on choices made in cells H251 - H254 on the SW Worksheet," & _
        vbCrLf & "your options are now visible here." & _
        vbCrLf & vbCrLf _
        & "If no options appear here, change your choices on the SW Worksheet."
End Sub

Open in new window

  • 2
1 Solution
Rory ArchibaldCommented:
Make row 165 blank. :)
nbozzyAuthor Commented:
Shoot me! You know, rorya, I actually did have it blank at one point, but I changed my code since then. Silly me.....thanks so much!
nbozzyAuthor Commented:
Glad you were there for me!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now