Macro to unhide rows if the values in a range are numeric and hide rows that are non numeric from the first appearance of a non numeric result

[chappy]

I would like to create a macro to Loop through a range from cell AH4 to cell AH10000 on Sheet7 in a workbook (sheet name "CALCULATIONS"). I want to ensure that for all cells in the range AH4 to AH10000 that include numeric values, the row for that cell is unhidden. In addition I want to ensure that for all cells in the range that are non numeric, the row for that cell is hidden. The data is sequential in that after the first non numeric result occurs, all the following cells to the bottom of the range will also contain a non numeric result (either text "No data" or an error value).

I have tested a lot of different code to try and resolve this but haven't managed to make anything that works well. My initial thought was to use Do Until in relation to the first appearance of a non numeric result, from that point it should be possible to select all rows from the current row to row 10000 and hide them in a single action. That seems an efficient way to approach this if it stops the loop through every remaining row in the range. However, I am stuck. If anyone can assist I would be very grateful

 

[JLGWhiz]

Sub t()
With ActiveSheet
    .Rows.Hidden = False
    .Range("AH4:AH1000").SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Hidden = True
End With
End Sub

 

[jasonb75]

Why loop? Assuming header in AH3

ActiveSheet.Range("AH3:AH1000").AutoFilter Field:=1, Criteria1:="<>?*", Operator:=xlAnd, Criteria2:="<>"

 

[Peter_SSs]

Looks like both suggestions may have omitted ..

all the following cells to the bottom of the range will also contain a non numeric result (either text "No data" or an error value).

If column AH are constant values, try

Range("AH4:AH10000").SpecialCells(xlCellTypeConstants, 18).EntireRow.Hidden = True

If formulas ..

Range("AH4:AH10000").SpecialCells(xlCellTypeFormulas, 18).EntireRow.Hidden = True

 

[chappy]

JLGWhiz, jasonb75, Peter_SSs

Thank you all for your responses - All three of you have helped a lot. From your answers I realise that I probably left out some key information. The column I am referring to includes a number based on formula. Cell AH4 will always contain 1 and the numbers thereafter are sequential. As a result (if fully populated) the cells would be as follows AH4 = 1, AH5 = 2, down to AH10000 = 9996. Therefore I want to hide any rows that do not have a value between 1 and 9996.

I have adjusted based on your responses and come up with the following which has worked through initial tests at least. Many thanks again for your help and fast replies!

Sub HideCalculationRows() 'Hide rows on "CALCULATIONS" sheet'

'Set Calculations sheet in a variable'
Set calculationsSheet = ActiveWorkbook.Sheets("CALCULATIONS")

calculationsSheet.Activate

With ActiveSheet
    .Rows.Hidden = False
    .AutoFilterMode = False
    .Range("AH3:AH10000").AutoFilter
    .Range("AH3:AH1000").AutoFilter Field:=1, Criteria1:=">=0", Operator:=xlAnd, Criteria2:="<=9996"

End With

End Sub

 

[JLGWhiz]

Looks like both suggestions may have omitted ..


If column AH are constant values, try

Range("AH4:AH10000").SpecialCells(xlCellTypeConstants, 18).EntireRow.Hidden = True

If formulas ..

Range("AH4:AH10000").SpecialCells(xlCellTypeFormulas, 18).EntireRow.Hidden = True

I had tried that, but two things did not fall into place. One was that if formulas were used to create the mumerical sequence, then all the rows would be hidden and the other thing was that VBA did not seem to recognize the Excel error codes in cells with formulas, which seemed odd.

 

[Peter_SSs]

One was that if formulas were used to create the mumerical sequence, then all the rows would be hidden and the other thing was that VBA did not seem to recognize the Excel error codes in cells with formulas, which seemed odd.

I did not have either experience. Argument of 18 in SpecialCells is Text Values + Error values. Numerical values returned by formula remained visible while text and error formula results were hidden.

 

[JLGWhiz]

I did not have either experience. Argument of 18 in SpecialCells is Text Values + Error values. Numerical values returned by formula remained visible while text and error formula results were hidden.

Yes, using 18 it hid the text rows OK, but left the error values. I will have to reconstruct and see where I might have fouled up. cul.

 

[Peter_SSs]

I will have to reconstruct and see where I might have fouled up.

My test setup was like this & I tested on column AH for formulas and AG for constants.

20 03 01.xlsm
	AG	AH
3	Hdr AG	Hdr AH
4	3	3
5	5	5
6	6	6
7	9	9
8	x	x
9	e	e
10	w	w
11	#N/A	#N/A
12	#N/A	#N/A
13	#N/A	#N/A
14	#N/A	#N/A
Hide rows

Cell Formulas
Range	Formula
AH4:AH14	AH4	=AG4

 

[JLGWhiz]

This is the set up I have.

TestBase.xlsm
	AF	AG	AH
1			1
2			2
3			3
4			4
5			5
6			6
7			7
8			8
9			a
10			b
11			c
12			d
13			e
14			#VALUE!
15			#VALUE!
16			#VALUE!
17			0
Sheet1

Cell Formulas
Range	Formula
AH14:AH17	AH14	=Y2+Z2

and this is the code

Sub t()
Range("AH2", Cells(Rows.Count, "AH").End(xlUp)).SpecialCells(xlCellTypeConstants, 18).EntireRow.Hidden = True
End Sub

This is the result;

TestBase.xlsm
	AF	AG	AH	AI
1			1
2			2
3			3
4			4
5			5
6			6
7			7
8			8
14			#VALUE!
15			#VALUE!
16			#VALUE!
17			0
Sheet1

Cell Formulas
Range	Formula
AH14:AH17	AH14	=Y2+Z2

So I seem to have a systemic problem somewhere.