Excel Macro that can hide rows is select cell value = 0

Question

Hello,

We are trying to figure out how to set up a macro that would hide trows when the values in columns E, F, G, H, I J, K,L ALL equal zero. 

We have figured out a macro to hide rows when ANY zeros shows up but the HIDE rule needs to happen only if all conditions are met.

The entire data set encompasses A2:L3955

In the example below, the rows that are in bold would be hidden in our ideal world.

Can any experts out there help?

Thanks!

A	B	C	D	E	F	G	H	I	J	K	L
Date	Title Id	Title Name	Offer type	Minutes Streamed - DE	Minutes Streamed - JP	Minutes Streamed - UK	Minutes Streamed - US	Unique Streams - DE	Unique Streams - JP	Unique Streams - UK	Unique Streams - US
8/19/2017	PX8ERMH	Title 1	FREE	979.591	123.053	1832.233	14013.03	80	43	103	549
8/20/2017	PX8ERMH	Title 1	FREE	1739.417	164.832	3048.624	20056.99	86	39	133	771
8/21/2017	PX8ERMH	Title 1	FREE	795.647	58.84	1248.074	15475.82	53	25	86	625
8/22/2017	PX8ERMH	Title 1	FREE	0	0	0	6395.329	0	0	0	253
8/23/2017	PX8ERMH	Title 1	FREE	0	0	0	0	0	0	0	0
8/1/2017	PX8ERMH	Title 1	RENTAL	0	0	0	114.202	0	0	0	8
8/2**/2017**	PX8ERMH	Title 1	RENTAL	0	0	0	0	0	0	0	0
8/3/2017	PX8ERMH	Title 1	RENTAL	0	0	0	0	0	0	0	0
8/4/2017	PX8ERMH	Title 1	RENTAL	0	0	0	0	0	0	0	0

Beth

Answer 1

OK. I am assuming that column Q is an unused column. If this is not correct then everywhere in the code that references column Q must be edited to the reference of a blank column. Also the code line where the AutoFilter is set "Field:=17" must be edited to reflect the number of the column (Column 17 is column "Q").

Replace all of the previous code with the following and then Run Macro1 (If you use F5 to run the code then the cursor should be somewhere between Sub Macro1() and the End Sub for that macro name. If the cursor is not located within the sub then you will get a pop up dialog with the option to select Macro1 and then click the Run button.)

Sub Macro1()

    Dim ws As Worksheet

    Dim lngLastRow As Long

    Set ws = Worksheets("Sheet1")       'Edit "Sheet1" to your worksheet name

    'Find last used row on worksheet (ws.Cells is entire worksheet range)

    lngLastRow = LastRowOrCol(True, ws.Cells)

    With ws

        .AutoFilterMode = False     'Start by turning off AutoFilter (If already on)

        .Range("Q1") = "CountZeros"

        .Range("Q1").Font.Bold = True

        .Cells(2, "Q") = "=COUNTIF(I2:P2,0)"    'Enter formula in cell Q2

        'Copy the formula down to last row of data

        .Cells(2, "Q").Copy Destination:=.Range(.Cells(2, "Q"), .Cells(lngLastRow, "Q"))

        Application.CutCopyMode = False     'Need to cancel copy with latest update of Excel

        .Range(.Cells(1, "A"), .Cells(lngLastRow, "Q")).AutoFilter  'Turn on Autofilter

        .AutoFilter.Range.AutoFilter Field:=17, Criteria1:="<>8"    'Set filter NOT equal to 8

        .Columns("Q:Q").Hidden = True       'Optional to hide column "Q" containing formula

    End With

End Sub

Function LastRowOrCol(bolRowOrCol As Boolean, Optional rng As Range) As Long

    'Finds the last used row or column in a worksheet

    'First parameter is True for Last Row or False for last Column

    'Third parameter is optional

    'Must be specified if not ActiveSheet

    Dim lngRowCol As Long

    Dim rngToFind As Range

    If rng Is Nothing Then

        Set rng = ActiveSheet.Cells

    End If

    If bolRowOrCol Then

        lngRowCol = xlByRows

    Else

        lngRowCol = xlByColumns

    End If

    With rng

        Set rngToFind = rng.Find(What:="*", _

                LookIn:=xlFormulas, _

                LookAt:=xlPart, _

                SearchOrder:=lngRowCol, _

                SearchDirection:=xlPrevious, _

                MatchCase:=False)

    End With

    If Not rngToFind Is Nothing Then

        If bolRowOrCol Then

            LastRowOrCol = rngToFind.Row

        Else

            LastRowOrCol = rngToFind.Column

        End If

    End If

End Function

Answer 2

Hi Peth,

Did you refer to OssieMac's suggestions? Feel free to let us know if you need further assistance.

Thanks,

Tina

Answer 3

I actually wound up doing his OssieMac's first suggestion because I couldn't make the macro run which I attribute to my lack of knowledge.

This worked:

1. Add an additional column header in column M.   Header suggestion CountZeros
2. In cell M2 enter the formula    =COUNTIF(E2:L2,0)
3. Copy the formula down
4. Apply AutoFilter to the range of data
5. Select Dropdown on column M
6. Select Number filters (Above the list of options displayed)
7. Select Does not equal
8. Enter 8  (8 is the max number of zeros for the 8 columns)
9. Click OK

THANKS!

Answer 4

Hi Peth,

Thanks for your sharing. It will definitely benefit other who come across the thread.

If you have other questions, feel free to post a new thread in the forum.

Regards,

Tina