Require help with VBA code for hiding rows based on cells in range of columns being 0

[MonicaP]

Require help with VBA code for hiding rows based on cells in range of columns being 0
I'm found this code to use but when I use it, it hides all the rows. I need it to hide row if all cells in columns H through L show a 0 value. Can someone please help me with this.

Sub Hide_rows()

Dim LastRow As Long

Dim Rng As Range

LastRow = Range("A125").End(xlUp).Row '

Set Rng = Range("H3:L125")

Application.ScreenUpdating = False

For Each cell In Rng

If cell.Value = "0" Then

cell.EntireRow.Hidden = True

End If

Next cell

Application.ScreenUpdating = True

End Sub

 

[Joe4]

Welcome to the Board!

Does the possibility exist for blanks (empty cells) to be included in the data?
If so, are they to be treated as zeroes or not?

 

[MonicaP]

Welcome to the Board!

Does the possibility exist for blanks (empty cells) to be included in the data?
If so, are they to be treated as zeroes or not?

No, there would be a zero in the cell. It's formula based.

 

[Joe4]

OK, try this:

Sub MyHideRows()

    Dim r As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
'   Loop through all rows
    For r = 3 To 125
'       Set range to check
        Set rng = Range(Cells(r, "H"), Cells(r, "L"))
'       Count the number of zeroes in range
        If Application.WorksheetFunction.CountIf(rng, "=0") = 5 Then
'           Hide rows
            Rows(r).Hidden = True
        Else
            Rows(r).Hidden = False
        End If
    Next r

    Application.ScreenUpdating = True

End Sub

 

[MonicaP]

OK, try this:

Sub MyHideRows()

    Dim r As Long
    Dim rng As Range
   
    Application.ScreenUpdating = False
   
'   Loop through all rows
    For r = 3 To 125
'       Set range to check
        Set rng = Range(Cells(r, "H"), Cells(r, "L"))
'       Count the number of zeroes in range
        If Application.WorksheetFunction.CountIf(rng, "=0") = 5 Then
'           Hide rows
            Rows(r).Hidden = True
        Else
            Rows(r).Hidden = False
        End If
    Next r

    Application.ScreenUpdating = True

End Sub

Hi, sorry, this didn't hide anything. When I look at the Range (Cells(r, "H"), Cells(r, "L")) does that cover the columns in between? I'm trying to understand. I do appreciate your help. My sheet is actually over 11000 rows and I wanted to test on a small area first.

 

[Joe4]

When I look at the Range (Cells(r, "H"), Cells(r, "L")) does that cover the columns in between?

Yes it does.

Can you post a few things:
1. One of the formulas that is in these cells
2. A small sample of your data

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MonicaP]

Yes it does.

Can you post a few things:
1. One of the formulas that is in these cells
2. A small sample of your data

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

LOCATION 1 RECONCILIATION 2022.xlsx
	A	B	C		E	F	G	H	I	J	K	L	M	N	O
1		INSTRUCTIONS HERE ARE FOR WHEN ALL BILLING HAS BEEN COMPLETED AND ALL INVENTORY ADJUSTMENTS HAVE BEEN MADE.					IMPORT AVG COST FOR LOCATION				IMPORT QTY AND VALUES FOR LOCATION
2	CODE	ITEMNO	DESC		CNTLACCT	STOCK UNIT	Accpac COST	Total Counted	Count Value		Accpac QTY	Accpac TL VALUE		variance QTY	variance VALUE
3	10001	008CRVGEL	EUROSEAL GEL (148 ml)		1	EA		15	0		0	0		15	0
4	10002	04615	STRUCTURAL ADH 1MIN		4	EA		0	0		0	0		0	0
5	10003	04616	STRUCTURAL ADH 5 MIN		4	EA		18	0		0	0		18	0
6	10004	04617	STRUCTURAL ADH 15 MIN		4	EA		0	0		0	0		0	0
7	10005	04618	STRUCTURAL ADH 40 MIN		4	EA		1	0		0	0		1	0
8	10006	04620	SEAM SEALER 10 MIN		1	EA		15	0		0	0		15	0
9	10007	04621	SELF LEVEL SEAM SLR		4	EA		0	0		0	0		0	0
10	10008	04622	URETHANE FOAM 2 PART		4	EA		6	0		0	0		6	0
11	10009	04623	PLASTIC PARTS CLEANER		4	EA		0	0		0	0		0	0
12	10010	04624	TPO ADH PRMTR BRUSH TOP		1	EA		0	0		0	0		0	0
13	10011	04625	TPO ADH PRMTR AEROSOL		1	EA		0	0		0	0		0	0
14	10012	04626	DBL CART APPLICATOR GUN		1	EA		0	0		0	0		0	0
15	10013	04627	MIXING NOZZLES 18 ELM		4	EA		0	0		0	0		0	0
16	10014	04628	MIXING NOZZLES 24 ELM		4	EA		0	0		0	0		0	0
17	10015	04628OUL	24 ELEMENT NOZZLES (GREEN INDIVIDUAL)		3	EA		0	0		0	0		0	0
18	10016	04631	STRUCT BACKING PATCH		4	EA		0	0		0	0		0	0
19	10017	04632	FIBERGLASS RNFRCG TAPE		1	EA		0	0		0	0		0	0
20	10018	04633	PLSTIC CONTR RELS FLM		4	EA		0	0		0	0		0	0
21	10019	04634	STRUCTURAL ADH START KIT		4	EA		0	0		0	0		0	0
22	10020	04647	STRUCTURAL ADHESIVE 30 SECOND		4	EA		0	0		0	0		0	0
23	10021	04671	SMC FIBERGLASS URETHANE ADH 400ML 3MIN		4	EA		0	0		0	0		0	0
24	10022	04672	SMC FIBERGLASS URETHANE ADH 10 MINUTE		4	EA		0	0		0	0		0	0
MASTER

Cell Formulas
Range	Formula
H3:H24	H3	=SUMIF(ENTRY!B:C,MASTER!$A3,ENTRY!C:C)
I3:I24	I3	=H3*G3
N3:O24	N3	=H3-K3

i hope this works

 

[Joe4]

The first thing I asked is if there is the possibility of blank cells in the range, and you said "No".
But all of column J is blank!

So even though that spans 5 columns of data, only 4 would ever have zeroes in them.
Therefore, you would need to change this line to:

        If Application.WorksheetFunction.CountIf(rng, "=0") = 5 Then

to this:

        If Application.WorksheetFunction.CountIf(rng, "=0") = 4 Then

 

[MonicaP]

The first thing I asked is if there is the possibility of blank cells in the range, and you said "No".
But all of column J is blank!

So even though that spans 5 columns of data, only 4 would ever have zeroes in them.
Therefore, you would need to change this line to:

        If Application.WorksheetFunction.CountIf(rng, "=0") = 5 Then

to this:

        If Application.WorksheetFunction.CountIf(rng, "=0") = 4 Then

The first thing I asked is if there is the possibility of blank cells in the range, and you said "No".
But all of column J is blank!

So even though that spans 5 columns of data, only 4 would ever have zeroes in them.
Therefore, you would need to change this line to:

        If Application.WorksheetFunction.CountIf(rng, "=0") = 5 Then

to this:

        If Application.WorksheetFunction.CountIf(rng, "=0") = 4 Then

My apologies, yes, I missed that. Your solution worked. Thank you very much. I appreciate your help!

 

[Joe4]

You are welcome.
Glad I was able to help!


You can see why I asked it now. That little detail can mean the difference between working and not working.
It is never a bad idea to post a small sample of your data so we can see that, or other little things that may be important.
Many times, the devil is in the details!