I know you can select visible cells with:

Range.SpecialCells(Excel.XlCellType.xlCellTypeVisi ble).Select

Is there a way to select the opposite of that? I need to set the hidden
cells values to null.

Thanks.

Try:

Sub hidden_stuff()
Dim rHidden As Range
Set rHidden = Nothing
For Each r In Selection
If r.FormulaHidden = True Then
If rHidden Is Nothing Then
Set rHidden = r
Else
Set rHidden = Union(rHidden, r)
End If
End If
Next
If rHidden Is Nothing Then
Else
MsgBox (rHidden.Address)
End If
End Sub

--
Gary's Student
gsnu200703


"ALV" wrote:

I know you can select visible cells with:

Range.SpecialCells(Excel.XlCellType.xlCellTypeVisi ble).Select

Is there a way to select the opposite of that? I need to set the hidden
cells values to null.

Thanks.

Thanks Gary.

When I run this, cells in hidden and filtered rows don't have FormulaHidden
set to True, so nothing is selected.

In my current code I run through the range row-by-row and check if EntireRow
is hidden, but this kills performance. On data sets over a hundred thousand
rows it can take a half hour.


"Gary''s Student" wrote:

Try:

Sub hidden_stuff()
Dim rHidden As Range
Set rHidden = Nothing
For Each r In Selection
If r.FormulaHidden = True Then
If rHidden Is Nothing Then
Set rHidden = r
Else
Set rHidden = Union(rHidden, r)
End If
End If
Next
If rHidden Is Nothing Then
Else
MsgBox (rHidden.Address)
End If
End Sub

--
Gary's Student
gsnu200703


"ALV" wrote:

I know you can select visible cells with:

Range.SpecialCells(Excel.XlCellType.xlCellTypeVisi ble).Select

Is there a way to select the opposite of that? I need to set the hidden
cells values to null.

Thanks.