How to check and select within hidden cells only

[Maria J-son]

Hi, hope somebody work during this summer days and see this...

I need to make a check if there is hidden cells with values (containing
constants or formulas ) - if they are, i want to select them and make their
rows and columns visible. The hidden cells are created by hide columns and
rows, not by filtering.

Find filled VISIBLE cells are easy made with like routine below - but please
help me with checking the HIDDEN cells only, added by making the hidden rows
and columns of the filled cells visible:


Sub INV_MarkCellsWithValues()
'**************************
'Find filled (containing constants or formulas ) visible cells within named
range "......NOTVAL_ALL" (consist of several dynamic
'named ranges in active sheet differentiated with prefix "INV_" plus
codename
'of active sheet. Active sheet108 make named range:
""INV_Sheet108_NOTVAL_ALL"

Dim selConstForm As Range
Dim shName As String

If ActiveSheet.CodeName = "Sheet108" Then
shName = NOW
ElseIf ActiveSheet.CodeName = "Sheet109" Then
shName = "ALT2"
ElseIf ActiveSheet.CodeName = "Sheet110" Then
shName = "ALT3"
ElseIf ActiveSheet.CodeName = "Sheet111" Then
shName = "ALT4"
End If

Set selConstForm = Union(Range("INV_" & shName &
"_NOTVAL_ALL").SpecialCells(xlCellTypeConstants, 23), Range("INV_" & shName
& "_NOTVAL_ALL").SpecialCells(xlCellTypeFormulas, 23))
selConstForm.Select
End Sub



/Kind regards

 

[Mike Fogleman]

Once you have found the Union and set the range, unhide the column & row of
the range. The Union is in fact being found by your code. However you cannot
see the selection of hidden cells. They do not need to be visible for VB to
use them. Being Visible is for our own benefit.

Set selConstForm = Union(Range("INV_" & shName &
"_NOTVAL_ALL").SpecialCells(xlCellTypeConstants, 23), Range("INV_" & shName
& "_NOTVAL_ALL").SpecialCells(xlCellTypeFormulas, 23))
selConstForm.EntireColumn.Hidden = False
selConstForm.EntireRow.Hidden = False
selConstForm.Select

Mike F