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

hello,

we trying figure out how set macro hide trows when values in columns e, f, g, h, j, k,l all equal zero. 

we have figured out macro hide rows when zeros shows hide rule needs happen if conditions met.

the entire data set encompasses a2:l3955

in example below, rows in bold hidden in our ideal world.

can 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

probably don't need macro.

1. add additional column header in column m.   header suggestion countzeros
2. in cell m2 enter formula    =countif(e2:l2,0)
3. copy formula down
4. apply autofilter range of data
5. select dropdown on column m
6. select number filters (above list of options displayed)
7. select not equal
8. enter 8  (8 max number of zeros 8 columns)
9. click ok

however, if want above vba code following.

sub macro1()
    dim ws worksheet
    dim lnglastrow long
   
    set ws = worksheets("sheet1")       'edit "sheet1" worksheet name
   
    'find last used row on worksheet (ws.cells entire worksheet range)
    lnglastrow = lastroworcol(true, ws.cells)
   
    ws
        .autofiltermode = false     'start turning off autofilter (if on)
        .range("m1") = "countzeros"
        .range("m1").font.bold = true
        .cells(2, "m") = "=countif(e2:l2,0)"    'enter formula in cell m2
       
        'copy formula down last row of data
        .cells(2, "m").copy destination:=.range(.cells(2, "m"), .cells(lnglastrow, "m"))
        application.cutcopymode = false     'need cancel copy 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 8

        .columns("m:m").hidden = true   'optional hide column "m" formula  (line added after initial post)

    end with
       
end sub

function lastroworcol(bolroworcol boolean, optional rng range) long
    'finds last used row or column in worksheet
    'first parameter true last row or false last column
    'third parameter optional
    'must specified if not activesheet
   
    dim lngrowcol long
    dim rngtofind range
   
    if rng nothing then
        set rng = activesheet.cells
    end if
   
    if bolroworcol then
        lngrowcol = xlbyrows
    else
        lngrowcol = xlbycolumns
    end if
   
    rng
        set rngtofind = rng.find(what:="*", _
                lookin:=xlformulas, _
                lookat:=xlpart, _
                searchorder:=lngrowcol, _
                searchdirection:=xlprevious, _
                matchcase:=false)
    end with
   
    if not rngtofind nothing then
        if bolroworcol then
            lastroworcol = rngtofind.row
        else
            lastroworcol = rngtofind.column
        end if
    end if
   
end function

Office / Excel / Other/unknown / Office 365 for business