Filter on multiple columns

I have a spreadsheet that contains several thousand rows.

Each row contains a Group (Col C), Sub-Group (Col D), and numerous columns (headers represent calendar months) which contain receipts and expenditures in those Group/Sub-Group categories for each month.

Using VBA, I need to filter this worksheet on specific values of Group (=100) and sub-Group (=1) and then exclude records where all of the month columns (columns E - I) are equal to 0.

I've attached a workbook with sample data.
ComplexFilter.xlsm
LVL 49
Dale FyeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor Commented:
Another approach:

Sub MakeFilter()
    
    Dim Counter As Long
    Dim LastC As Long
    Dim LastR As Long
    
    Const GroupValue As Long = 100
    Const Subgroup As Long = 1
    Const MonthAmt As Long = 0
    Const FirstMonthCol As Long = 5
    Const LastMonthCol As Long = 9
    
    With ThisWorkbook.Worksheets("Trial Balance")
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        LastC = .Cells(4, .Columns.Count).End(xlToLeft).Column + 1
        .Cells(4, LastC) = "Check"
        .Range(.Cells(5, LastC), .Cells(LastR, LastC)).Formula = "=SUM(" & _
            .Cells(5, FirstMonthCol).Address(False, False) & ":" & _
            .Cells(5, LastMonthCol).Address(False, False) & ")"
        .[a4].AutoFilter
        .[a4].AutoFilter Field:=3, Criteria1:=GroupValue, Operator:=xlAnd
        .[a4].AutoFilter Field:=4, Criteria1:=Subgroup, Operator:=xlAnd
        .[a4].AutoFilter Field:=LastC, Criteria1:="<>" & MonthAmt, Operator:=xlAnd
    End With
    
End Sub

Open in new window

0
 
dlmilleCommented:
While you can do this with advanced filter, controlling criteria on the spreadsheet, you've explicitly asked for VBA, so the app i write clears any existing filters on the worksheet, defines the db range for filtering, turns on the filter, then sets the filter for Group, subGroup, and, in a loop, the month columns.

I also added a simple macro to turn the filter off, as needed.

Both can be accessed thru buttons on the primary worksheet.

Here's the code:
 
Sub filterMacro()
Dim wkb As Workbook
Dim sht As Worksheet
Dim dbRange As Range
Dim dbStartCell As Range
Dim grpCol As Long
Dim subGrpCol As Long
Dim mnthCol As Long

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
    Set dbStartCell = sht.Range("A4")
    
    Set dbRange = sht.Range(dbStartCell, sht.Range("A" & Rows.Count).End(xlUp))
    Set dbRange = sht.Range(dbRange, sht.Cells(dbStartCell.Row, Columns.Count).End(xlToLeft))
    
    grpCol = 3
    subGrpCol = 4
    
    dbRange.AutoFilter field:=grpCol, Criteria1:="100"
    dbRange.AutoFilter field:=subGrpCol, Criteria1:="1"
    
    For mnthCol = sht.Range("E1").Column To sht.Range("I1").Column
        dbRange.AutoFilter field:=mnthCol, Criteria1:="-"
    Next mnthCol
    
End Sub
Sub filterOff()
Dim wkb As Workbook
Dim sht As Worksheet

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
End Sub

Open in new window


See attached workbook.

PS - note, wkb points to ThisWorkbook - that's the workbook where this code resides.  If you want to rather run the app in this workbook on another workbook, just change the statement:

set wkb = ThisWorkbook

to

set wkb = ActiveWorkbook

Cheers,

Dave
ComplexFilter-r1.xlsm
0
 
Dale FyeAuthor Commented:
Turns out I was premature in my selection of a solution.  I failed to realize Dave's solution was filtering on "=0" in each of the monthly columns.  This results in getting only those rows which contain all zeros.  What I needed was those rows that contain any non-zero value, and I cannot figure out how to do that using Dave's solution.

Although I would prefer not to add a column to the spreadsheet, as Patrick has done, I cannot figure out any other way to do this.  I've replaced the SUM() function with a CountIF() function and am now testing for that column <> 0.
0
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.

 
dlmilleCommented:
Ah, I misread.  Only need to change the criteria to >0 as opposed to =0

That would be changing this line:

        dbRange.AutoFilter field:=mnthCol, Criteria1:="<>0"

See attached,

Dave
ComplexFilter-r2.xlsm
0
 
Dale FyeAuthor Commented:
Dave,

What your original code gave me was:

"E = 0 and F = 0 and G = 0 and H = 0 and I = 0"

Your new recommendation ("<> 0") gets me:

"E <> 0 and F <> 0 and G <> 0 and H <> 0 and I <> 0"

when what I really want is:

 "E <> 0 or F <> 0 or G <> 0 or H <> 0 or I <> 0"

What I'm using now, which is working quite well is a formula in the L column, which is hidden to the users.  It looks like:

=AND(IF($C5=L$2, TRUE, FALSE), IF($D5=L$3, TRUE, FALSE), IF(COUNTIF($E5:$I5,"<> 0") > 0, TRUE, FALSE))

With this, I just push the value of the Group to "L2" and of the sub-group to "L3".

Then I filter the sheet on the value in column L = true


0
 
dlmilleConnect With a Mentor Commented:
Ahh -  sorry, I'm having a Monday morning without enough coffee!

Thanks for the clarification...

So E <>0 or F <> 0 or G <> 0 or H <> 0 or I <> 0 is exactly the same as NOT (E=0 and F=0 and G=0 and H=0 and I=0).  That's a bit hard to do without a helper column, as matthewspatrick provided - however, it is also the same as SUM(E:I)>0.

The attached provides that solution, without a helper, though I'm starting to like matthewspatrick's solution, more, as its easier to implement, though I understand not wanting to have that column (could delete it after?)

At any rate, here's my revised code:
Sub filterMacro()
Dim wkb As Workbook
Dim sht As Worksheet
Dim dbRange As Range
Dim dbStartCell As Range
Dim grpCol As Long
Dim subGrpCol As Long
Dim mnthCol As Long
Dim sumRow As Long
Dim sumThis As Range, mySum As Double
Dim dbNoHeader As Range

    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
    Set dbStartCell = sht.Range("A4")
    
    Set dbRange = sht.Range(dbStartCell, sht.Range("A" & Rows.Count).End(xlUp))
    Set dbRange = sht.Range(dbRange, sht.Cells(dbStartCell.Row, Columns.Count).End(xlToLeft))
    
    Set dbNoHeader = dbRange.Offset(1, 4).Resize(dbRange.Rows.Count - 1, 5) ' just the data, for hiding rows
    
    grpCol = 3
    subGrpCol = 4
    
    dbRange.AutoFilter field:=grpCol, Criteria1:="100"
    dbRange.AutoFilter field:=subGrpCol, Criteria1:="1"
    
    'now determine if rows should be hidden based on critiera
    For sumRow = 1 To dbNoHeader.Rows.Count
        Set sumThis = sht.Range(dbNoHeader.Cells(sumRow, 1), dbNoHeader.Cells(sumRow, dbNoHeader.Columns.Count))
        Debug.Print sumThis.Address, Application.WorksheetFunction.Sum(sumThis)
        mySum = Application.WorksheetFunction.Sum(sumThis)
        If mySum = 0 Then
            sht.Cells(sumRow + dbNoHeader.Cells(1, 1).Row - 1, 1).EntireRow.Hidden = True
        End If
    Next sumRow
    
    Application.ScreenUpdating = True
End Sub
Sub filterOff()
Dim wkb As Workbook
Dim sht As Worksheet

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Trial Balance")
    
    sht.AutoFilterMode = False
    
End Sub

Open in new window


See attached,

Dave
ComplexFilter-r3.xlsm
0
 
Dale FyeAuthor Commented:
Dave,

Your assessment that what I'm looking for (logically) is the same as:

"NOT (E=0 and F=0 and G=0 and H=0 and I=0). "

is correct.

I like the technique of using EntireRow.Hidden and will keep that in my bag of tricks.

But I'm comfortable with what I have working now.  Thanks for the help.
0
 
dlmilleCommented:
I must post one more time.  I finally understand what you mean.

If Countif(sumrange,"<>0") then don't hide it.

Here's the solution.

Cheers,

Dave
ComplexFilter-r4.xlsm
0
All Courses

From novice to tech pro — start learning today.