Workaround for filtering for multiple wildcard criteria

Question

I saw a few different solutions for getting around the 2 wildcard limit for filters in excel (specifically the answer posted here, but I had a hard time understanding what they were doing. The work around I am trying to implement is to just count the number of instances of each criteria in a cell using a basic countif formula, and if it's 0 then the macro will hide the row.

With ws1

    ColumnOne = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LastRow = .Cells(.Rows.Count, lastColumn).End(xlUp).Row

    ColumnTwo = ColumnOne - 1



    If ws2.Application.WorksheetFunction.CountA("D47:D61") <> 0 Then

        For i = 1 To LastRow
            If .Cells(i, ColumnTwo).Value = 0 Then
                .Rows(i).EntireRow.Hidden = True
            End If

        Next i

    ElseIf ws2.Application.WorksheetFunction.CountA("E47:E61") <> 0 Then

        For i = 1 To LastRow
            If .Cells(i, ColumnOne).Value = 0 Then
                .Rows(i).EntireRow.Hidden = True
            End If

        Next i
    Else
        Exit Sub
    End If
End With

The above code is what I'm using to deal with this. For reference, there are two columns of criteria on a separate worksheet (ws2) that can change/be entirely empty. The code above then checks to see which of these columns it should work with (D on ws2 uses ColumnTwo on ws1, E on ws2 uses ColumnOne on ws1) by seeing which one isn't empty, then begins checking the column on ws1 row by row to see if any of the criteria are present. If none of the criteria are present (cell value = 0) then it should hide that row.

The check to use either D or E does not seem to be working. Even when all of the cells in D47:D61 on ws2 are empty it is still operating within that if statement instead of moving to E47:E61. Any ideas as to what I should try and change here?

Can you be more specific about what isn't working? The code looks OK, what problem are you experiencing? — Absinthe
Ah sorry I should have been more specific - currently no rows are being hidden, even when they should be. I've set up an example where the columns where it checks for 0's have plenty present, but for some reason the rows aren't being hidden. — Valkeif
I'm also having an issue where everything is being hidden when checking ColumnTwo, even though there are plenty of cells with values greater than 0. — Valkeif
I'm pretty sure the issue is with the check itself, as even when D47:D61 is empty it is still working inside that first if statement. Maybe it's not referencing the worksheets as I originally wanted? D47:D61 is on ws2, but the check for the 1's and 0's (and the row hiding itself) happens on ws1. — Valkeif
Where does lastColumn get assigned a value (3rd line down)? Add a breakpoint (wiseowl.co.uk/blog/s196/breakpoints.htm ) on the 4th line - what is the value of lastRow when you hover your mouse over it? Also if the 2nd part of the IF statement is supposed to check sheet 2 you can't use .Cells, you need to add the sheet e.g. ws2.Cells — Absinthe

GMalc GMalc · Accepted Answer · 2019-02-20T17:23:19

I modified your code to ensure all variables are declared.

Sub HideRows()
'Ensure you declare all your variables
Dim ws1 As Worksheet, ws2 As Worksheet, lRow As Long
Dim ColumnOne As Integer, ColumnTwo As Integer
Dim dRng As Range, eRng As Range
Dim dRngCnt As Long, eRngCnt As Long

'Assign worksheets and variables
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

'Identifying the specific range as variable
Set dRng = ws2.Range("D2:D9")
Set eRng = ws2.Range("E2:E9")

'Assigning a variable to the countA will simplify your IF and ELSEIF statements
dRngCnt = Application.WorksheetFunction.CountA(dRng)
eRngCnt = Application.WorksheetFunction.CountA(eRng)

    With ws1
        ColumnOne = .Cells(1, .Columns.Count).End(xlToLeft).Column
        'I replaced "lastColumn" with "ColumnOne", because they are the same value, so you only need to use one
        lRow = .Cells(.Rows.Count, ColumnOne).End(xlUp).Row
        ColumnTwo = ColumnOne - 1

        If dRngCnt <> 0 Then 'I use the countA variable for column D Range
            For i = 1 To lRow
                If .Cells(i, ColumnTwo).Value = 0 Then 'Any 0s in the second to last columns will hide the row
                    .Rows(i).EntireRow.Hidden = True
                End If
            Next i

        ElseIf eRngCnt <> 0 Then 'I use the countA variable for column E Range
            For i = 1 To lRow
                If .Cells(i, ColumnOne).Value = 0 Then
                    .Rows(i).EntireRow.Hidden = True
                End If

            Next i
        Else
            Exit Sub

        End If
    End With

End Sub

Workaround for filtering for multiple wildcard criteria

2 Answers