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

New Member
Joined
Jul 18, 2006
Messages
36
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
 
@Peter SSs
It does work using the xlCellTypeFormulas when all cells in the ramge are formulas. But not when there is a mixture of constants and formulas and xlCellTypeConstants is use for SpecialCells. So I guess there is a limitation on when and how combinations with xlErrors can be used.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Rich (BB code):
.SpecialCells(xlCellTypeConstants, 18).EntireRow.Hidden = True
Rows 14:17 are not Constants, they are Formulas. :)
 
Upvote 0
Rich (BB code):
.SpecialCells(xlCellTypeConstants, 18).EntireRow.Hidden = True
Rows 14:17 are not Constants, they are Formulas. :)
Yes, I was just taking the spec sheet statement about adding the attribute values as being able to apply to a mixed range, but I see now that won't work. A misinterpretation on my part.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top