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

Probably don't need a macro.

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

However, if you want to do the above with VBA code then the following.

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("M1") = "CountZeros"

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

        .Cells(2, "M") = "=COUNTIF(E2:L2,0)"    'Enter formula in cell M2

        'Copy the formula down to last row of data

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

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

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

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

        .Columns("M:M").Hidden = True   'Optional to hide column "M" with formula  (Line added after initial post)

    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

Note that I have edited the code in my previous post and added the bolded line towards the end of the Sub Macro1() to hide the added column "M".

This post is because otherwise you do not get a notification re the edited post.

Answer 3

OssieMac - 

This is so helpful...almost.  I'm new to VMAs so forgive my ignorance.

My understanding is that I apply this code here (image inserted)....but when I enter F5 i get the message: "subscript out of range"

What line(s) do I need to modify? The report I'm applying this to is a video streaming report that updates daily across multiple titles so that data fields increase daily. For example there are now a total of 4655 rows

Also, in the sample I provided you, I omitted columns (as I didn't want sensitive business data to be public) - does that matter.  The total column #s does not change with the report - columns A - P are active.

THANK YOU!!!

Answer 4

Also, in the sample I provided you, I omitted columns (as I didn't want sensitive business data to be public) - does that matter.  The total column #s does not change with the report - columns A - P are active.

Yes! It is extremely important that the number of columns matches the code that I provided. It is also necessary that the columns containing the zeros are correct.

The code that I provided uses column M to total the number of columns with zero which you previously told me were E, F, G, H, I J, K,L.  Now you tell me that columns A:P are active so the totals in column M would have over written any previous data in the column.

Please confirm the following (Note that I am referring to column; not rows):

• The columns that contain data
• The last used column on the worksheet.
• The columns that contain the zeros so the correct columns can be referenced to count the zeros.

Answer 5

OssieMac,

My mistake.  In the original sample I had deleted columns D - G, as these columns are not relevant and are always empty of data. 

Below is the the first three rows of the latest report with those columns intact.

Columns I - P can contain the zeros. If all 8 columns = 0 than hide.

thank you.

Beth