Invert a range? - I wish SpecialCells(xlCellTypeHIDDEN) exist ...

[tskogstrom]

Hi,

I try another angle to solve my last question, becauser of lack of
answers

Any suggestion how all hidden values become "100%" as soon as the rows
become hidden. (... by my code
"Columns("AE:AE").SpecialCells(xlCellTypeFormulas, 4).EntireRow.Hidden
= True" in a routine. )

Range("P20:Y100").SpecialCells(xlCellTypeVisible).Value = "100%" would
be greate to use, if I could change it to "xlCellTypeHidden" instead,
but ...

Any idea how to "invert" a selection, maybe? I have a larger range and
one range with xlCellTypeVisible identified - how to find out the
hidden cells?


Any ideas?

/Regards
Tskogstrom

 

[Guest]

As you have discovered, xlCellTypeVisible are easy to select because they are
a defined special type. This is how to select or define the "invisible"
cells in a range:

Sub find_invisible()
Dim r As Range, r_loop As Range
Dim r_visible As Range
Dim r_invisible As Range

Set r = Selection
Set r_visible = r.SpecialCells(xlCellTypeVisible)
Set r_invisible = Nothing

For Each r_loop In r
If Intersect(r_loop, r_visible) Is Nothing Then
If r_invisible Is Nothing Then
Set r_invisible = r_loop
Else
Set r_invisible = Union(r_invisible, r_loop)
End If
End If
Next
MsgBox (r_visible.Address)
If r_invisible Is Nothing Then
Else
MsgBox (r_invisible.Address)
End If
End Sub