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
29
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
@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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Rich (BB code):
.SpecialCells(xlCellTypeConstants, 18).EntireRow.Hidden = True
Rows 14:17 are not Constants, they are Formulas. :)
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,828
Messages
5,627,135
Members
416,223
Latest member
RichardHell

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
Top