VBA cannot AutoFilter a Range for a certain criteria-CodePudding

I am trying to make a Range object of all entrys and than apply a filter, which searches for a number in there. I want the Range to hold only the matching entrys afterwards, but I always get the error 1004...

Here the code:

Dim rSearch As Range
Dim rResult As Range

Set rSearch = wbMe.Sheets(iCurSheet).Range("F2:F1000")
rSearch.AutoFilter Field:=iColKey, Criteria1:="=" & wbMe.Sheets(iCurSheet).Cells(iLine, iColKey).Value

The last line throws the exception. I found out that the AutoFilter has to be applied to the first line, so .Range("A1:K1"), but I still don't get why I am not able to Filter on a Range, maybe i get the Object wrong?

Thanks in advance!

Edit: So I tried some stuff:

Set rSearch = wbMe.Sheets(iCurSheet).Range("A2:K1000")
rSearch.AutoFilter Field:=11, Criteria1:="=" & wbMe.Sheets(iCurSheet).Cells(iLine, iColKey).Value
MsgBox "Count Rows rSearch:" & rSearch.Rows.Count

I expected the MsgBox to say smth less, but I get 999, so it hasn't filtered anything. My guess that I was filtering the wrong column, but I wanna filter on Col K (I need Col F afterwards to search once more, sry for mixing stuff up).

Now I don't get the AutoFilter exception anymore. But for some reason my rSearch range does not shrink. How do I shrink my Range?

CodePudding user response：

Count Visible Data Cells (Criteria Cells)

A quick fix could be something like

MsgBox "Count Rows rSearch:" & rSearch.Columns(11).SpecialCells(xlCellTypeVisible).Cells.Count - 1

Note that the headers need to be included in the range for AutoFilter to work correctly.

Using SpecialCells

Sub CountVisibleDataCells()

    ' Define constants.
    Const CriteriaIndex As Long = 11
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    ' Clear worksheet filters.
    If ws.FilterMode Then ws.ShowAllData
    ' Reference the range ('rg').
    Dim rg As Range: Set rg = ws.Range("A1:K21")
    
    ' Store the criteria value from the cell, converted to a string ('CStr'),
    ' in a string variable ('Criteria'). AutoFilter 'prefers' this.
    Dim Criteria As String: Criteria = CStr(ws.Range("M1").Value)
    
    ' Filter the range.
    rg.AutoFilter Field:=CriteriaIndex, Criteria1:=Criteria
    
    ' Reference the visible cells in the criteria column ('vrg').
    Dim vrg As Range
    Set vrg = rg.Columns(CriteriaIndex).SpecialCells(xlCellTypeVisible)
    
    ' Turn off the worksheet auto filter.
    ws.AutoFilterMode = False
    
    ' Store the number of visible cells of the criteria column
    ' in a long variable (subtract 1 to not count the header).
    Dim CriteriaCount As Long: CriteriaCount = vrg.Cells.Count - 1
    
    ' Inform.
    MsgBox "Count of '" & Criteria & "': " & CriteriaCount

End Sub

Using Application Count

Sub CountCriteriaCells()

    ' Define constants.
    Const CriteriaIndex As Long = 11
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    ' Clear worksheet filters.
    If ws.FilterMode Then ws.ShowAllData
    ' Reference the range ('rg').
    Dim rg As Range: Set rg = ws.Range("A1:K21")
    
    ' Store the criteria value from the cell, converted to a string ('CStr'),
    ' in a string variable ('Criteria').
    Dim Criteria As String: Criteria = CStr(ws.Range("M1").Value)
    ' You may need to modify this because 'CountIf' works differently.
    
    ' Reference the criteria data range ('cdrg') (no headers).
    Dim cdrg As Range
    With rg.Columns(CriteriaIndex)
        Set cdrg = .Resize(.Rows.Count - 1).Offset(1)
    End With
    
    ' Store the number of cells containing the criteria ('CriteriaCount')
    ' in a long variable.
    Dim CriteriaCount As Long
    CriteriaCount = Application.CountIf(cdrg, Criteria)
    
    ' Inform.
    MsgBox "Count of '" & Criteria & "': " & CriteriaCount

End Sub