autofilter, visible rows count error

[florin_excel]

Hi everyone,
I ran into problems while testing this bit of code, with erratic behaviour of the visible rows counter after autofilter. Please see comments on the attached workbook (autofilter_viz1.xlsm).
Any kind soul out there who can debug me, please?

Private Sub filter_Click()
' filter results logged in "data" wks

    Dim ws, wsUser As Worksheet
    Dim rRange As Range
    Dim lastRowWs As Long
    Dim myarray, max_x, max_y, max_z, x_criteria, y_criteria, z_criteria As String
    '
    Set wsUser = Sheets("user")
    Set ws = Sheets("data")
    ' set filter criteria
    max_x = wsUser.Range("max_x").Value
    max_y = wsUser.Range("max_y").Value
    max_z = wsUser.Range("max_z").Value
    x_criteria = "<" & max_x
    y_criteria = "<" & max_y
    z_criteria = "<" & max_z
    '
    lastRowWs = ws.Range("A" & Rows.Count).End(xlUp).Row     ' get last data cell in ws

    ' data headers in wsI A1:C1
    myarray = "A1:C" & CStr(lastRowWs)
    Set rRange = ws.Range(myarray)
    
    With ws
        .AutoFilterMode = False
        With rRange
            If x_criteria <> "<" Then ' this deals with no-value in max_x cell
                .AutoFilter Field:=1, Criteria1:=x_criteria
            End If
            If y_criteria <> "<" Then
                .AutoFilter Field:=2, Criteria1:=y_criteria
            End If
            If z_criteria <> "<" Then
                .AutoFilter Field:=3, Criteria1:=z_criteria
            End If
        End With
    End With

    MsgBox rRange.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 & " of " & rRange _
   .Rows.Count - 1 & " Records"

'
End Sub

[MarvinP]

Hi florin,

After finding zero hits after an autofilter, your data isn't expanded. The next time you find the LastRowWs it will return 1 instead of 12. I think this is why you get wrong answers.

To fix

After you display the number in your message box, simply unfilter the data and leave it all showing.

Put this after the msgbox

   With ws
        .AutoFilterMode = False
    End With