Row value using a variant

[Red over White]

I am trying to hide rows where the cells value in each of the cells is ErrNA for the cells in columns B to F, and then repeat the exercise over a number of rows. If any cell within B to F is not ErrNA (it will be a number) then I don't want to hide the row.

The program doesn't like the red line, and possibly not the following line. The program works if I replace RowVar(j) with the actual row number, but I would prefer not to have to write out this line numerous times to get the desired effect.

Where am I going wrong?

Sub Graphic2022()

Application.ScreenUpdating = False

Sheets("Graphic").Activate

Rows("2:100").EntireRow.Hidden = False

Dim j As Integer
Dim RowVar As Variant
RowVar = Array("2", "3", "4", "5", "6", "7", "8", "9")

For j = 0 To 7
If Cells(RowVar(j), 2).Value = CVErr(xlErrNA) And Cells(RowVar(j), 3).Value = CVErr(xlErrNA) And Cells(RowVar(j), 4).Value = CVErr(xlErrNA) And Cells(RowVar(j), 5).Value = CVErr(xlErrNA) And Cells(RowVar(j), 6).Value = CVErr(xlErrNA) Then
Rows(RowVar(j) & ":" & RowVar(j)).EntireRow.Hidden = True
End If
Next j

End sub

 

[Fluff]

Could you have any errors on those rows which are not #N/A?

 

[Red over White]

This is the relevant extract of the sheet

Graphic.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		2018	2019	2020	2021	2022		2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024
2	Area 1	24,476.03	24,811.69	24,225.27	20,509.40	#N/A		20,044.82	20,178.46	20,954.03	21,329.97	22,237.18	22,673.79	23,342.05	23,627.59	23,924.70	24,069.40	24,023.46	23,944.46	24,026.50	25,001.38	24,426.50	24,476.03	24,811.69	24,225.27	20,509.40	#N/A	#N/A	#N/A
3	Area 2	#N/A	#N/A	#N/A	5,432.98	16,210.61		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	5,432.98	16,210.61	#N/A	#N/A
4	Area 3	#N/A	#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
5	Area 4	2,878.60	3,824.80	3,544.58	3,395.24	1,667.03		1,734.51	1,698.00	1,975.74	3,319.76	2,880.10	3,336.29	3,799.89	3,750.03	2,609.06	4,957.98	3,254.33	3,346.02	3,595.73	2,940.62	3,325.66	2,878.60	3,824.80	3,544.58	3,395.24	1,667.03	#N/A	#N/A
6	Area 5	4,664.50	5,907.68	5,812.47	5,145.70	3,976.59		1,001.60	926.21	1,012.45	1,083.26	1,127.08	1,449.08	1,523.40	1,339.74	1,511.08	1,565.67	1,600.60	1,672.18	1,888.17	2,157.26	2,689.22	4,664.50	5,907.68	5,812.47	5,145.70	3,976.59	#N/A	#N/A
7	Area 6	1,889.64	1,624.34	2,584.85	5,030.44	2,424.68		727.54	777.49	1,284.67	8.29	132.70	374.35	140.92	9,224.54	2,335.92	2,435.59	2,747.75	3,321.14	3,642.69	3,764.80	4,607.83	1,889.64	1,624.34	2,584.85	5,030.44	2,424.68	#N/A	#N/A
8	Area 7	#N/A	#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A	389.99	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
9	Area 8	#N/A	#N/A	#N/A	#N/A	#N/A		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	138.06	276.18	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
10	Area 9	#N/A	#N/A	#N/A	#N/A	99.31																		#N/A	#N/A	#N/A	99.31	#N/A	#N/A
11	Area 10	#N/A	37.50	30.00	2.00	#N/A																		37.50	30.00	2.00	#N/A	#N/A	#N/A
12
Graphic

Cell Formulas
Range	Formula
B1:E1	B1	=C1-1
I1:AC1	I1	=H1+1
B2:F11	B2	=IF(LOOKUP(B$1,D$1:AE$1,D2:AE2)=0,NA(),LOOKUP(B$1,D$1:AE$1,D2:AE2))
H3:W3	H3	=IFERROR(IF(MAX(INDIRECT(H1&"!h4:h15"))=0,NA(),MAX(INDIRECT(H1&"!h4:h15"))-MAX(INDIRECT(H1&"!i4:i15"))),NA())
H4:W4	H4	=IFERROR(IF(OR(H1<2022,MAX(INDIRECT(H1&"!F4:F15"))=0),NA(),MAX(INDIRECT(H1&"!F4:F15"))-MAX(INDIRECT(H1&"!G4:G15"))),NA())
H8	H8	=NA()

 

[Fluff]

In that case try it like this

If IsError(Cells(RowVar(j), 2).Value) And IsError(Cells(RowVar(j), 3).Value) And IsError(Cells(RowVar(j), 4).Value) Then
   Rows(RowVar(j)).EntireRow.Hidden = True
End If

I've done the 1st 4 cols as an example

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Dim ErrorCount As Long, r As Long, c As Long
    For r = 9 To 2 Step -1
     ErrorCount = 0
     For c = 2 To 6
        If Application.IsNA(Cells(r, c).Value) Then ErrorCount = ErrorCount + 1
     Next c
        Rows(r).EntireRow.Hidden = ErrorCount = 5
    Next r

Dave

 

[Red over White]

Thank you - that works

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Red over White]

Thanks Dave, I hadn't seen your solution until after my previous posting. It also works, and has a certain symmetry which my original attempt lacked. I will doubtless return to yours when developing other macros.

 

[dmt32]

No worries & appreciate feedback - you found a solution which is what matters - glad we were able to help.

Dave