• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • 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."
        Range("H252").Select
        Exit Sub
    Else
        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 = ""
    Else
        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 = ""
    Else
        br = "Y"
    End If
    
    'set vol
    If IsEmpty(Range("H253")) Then
        vol = "x"
    Else
        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
        Range("J70").Select
        ActiveSheet.ShowAllData
    Else
        Range("G69:R164").Select
        Selection.AutoFilter
        Range("G70").Select
    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
    Else
        'ActiveSheet.Range("$G$69:$R$164").AutoFilter Field:=12, Criteria1:=br
        Selection.AutoFilter Field:=12, Criteria1:=br
    End If
    
    'volume
    If vol = "x" Then
        Selection.AutoFilter Field:=8
    Else
        Selection.AutoFilter Field:=8, Criteria1:=vol
    End If
    
    'sink
    If s = "x" Then
        Selection.AutoFilter Field:=9
    Else
        Selection.AutoFilter Field:=9, Criteria1:=s
    End If
    
    ActiveSheet.Range("D70").Select
    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

SOW-for-EE.xls
0
nbozzy
Asked:
nbozzy
  • 2
1 Solution
 
Rory ArchibaldCommented:
Make row 165 blank. :)
0
 
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!
0
 
nbozzyAuthor Commented:
Glad you were there for me!
0
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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