Can you use UsedRange.SpeciaCells() to loop through all Cells?

[ExcelMonkey]

I know I can use the .UsedRange property to loop through cells. I also know
I can use the UsedRange.SpecialCells() property to further define this. Is
it possible to use the SpecialCells() property and have it run as if you only
wanted the UsedRange by itself.

This loops through cells with formulas.
Thisworkbook.Worksheets("Sheet1").UsedRange.SpecialCells(xlformulas)

I want to continue to use this script but have the variable in brackets
after .SpecialCells mean all cells.
Thisworkbook.Worksheets("Sheet1").UsedRange.SpecialCells(something else)

I want to do this to avoid using two separate loops. I want one loop, and
then I want to pass a variable to the brackets after SpecialCells.

Make sense?

EM

 

[Rick Rothstein \(MVP - VB\)]

Can you use xlCellTypeVisible maybe?

Rick

 

[Dave Peterson]

Maybe you could build a range using .specialcells.

For instance, if I wanted to look at a range, but only wanted to loop through
the cells that were numeric and I didn't care if those numbers were the results
of formulas or just numeric constants, I could use:

Dim myNumConst As Range
Dim myNumFormulas As Range
Dim myNumCells As Range
Dim SearchRange as Range
dim rCell as range

Set SearchRange = activesheet.range("a1:x99")

Set myNumCells = Nothing
Set myNumConst = Nothing
Set myNumFormulas = Nothing

On Error Resume Next
Set myNumConst _
= SearchRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
Set myNumFormulas _
= SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If myNumConst Is Nothing Then
Set myNumCells = myNumFormulas
Else
If myNumFormulas Is Nothing Then
Set myNumCells = myNumConst
Else
Set myNumCells = Union(myNumConst, myNumFormulas)
End If
End If

If myNumCells Is Nothing Then
'do nothing
Else
'do the work against the smaller range
For Each rCell In myNumCells.Cells
'...
next rCell
End If

 

[ExcelMonkey]

Interesting. May give this a whirl.

Thanks

EM

Maybe you could build a range using .specialcells.

For instance, if I wanted to look at a range, but only wanted to loop through
the cells that were numeric and I didn't care if those numbers were the results
of formulas or just numeric constants, I could use:

Dim myNumConst As Range
Dim myNumFormulas As Range
Dim myNumCells As Range
Dim SearchRange as Range
dim rCell as range

Set SearchRange = activesheet.range("a1:x99")

Set myNumCells = Nothing
Set myNumConst = Nothing
Set myNumFormulas = Nothing

On Error Resume Next
Set myNumConst _
= SearchRange.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
Set myNumFormulas _
= SearchRange.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0

If myNumConst Is Nothing Then
Set myNumCells = myNumFormulas
Else
If myNumFormulas Is Nothing Then
Set myNumCells = myNumConst
Else
Set myNumCells = Union(myNumConst, myNumFormulas)
End If
End If

If myNumCells Is Nothing Then
'do nothing
Else
'do the work against the smaller range
For Each rCell In myNumCells.Cells
'...
next rCell
End If