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

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.Specia lCells(xlformulas)

I want to continue to use this script but have the variable in brackets
after .SpecialCells mean all cells.
Thisworkbook.Worksheets("Sheet1").UsedRange.Specia lCells(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

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

Can you use xlCellTypeVisible maybe?

Rick


"ExcelMonkey" wrote in message
...
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.Specia lCells(xlformulas)

I want to continue to use this script but have the variable in brackets
after .SpecialCells mean all cells.
Thisworkbook.Worksheets("Sheet1").UsedRange.Specia lCells(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

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

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 wrote:

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.Specia lCells(xlformulas)

I want to continue to use this script but have the variable in brackets
after .SpecialCells mean all cells.
Thisworkbook.Worksheets("Sheet1").UsedRange.Specia lCells(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

--

Dave Peterson

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

Interesting. May give this a whirl.

Thanks

EM

"Dave Peterson" wrote:

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 wrote:

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.Specia lCells(xlformulas)

I want to continue to use this script but have the variable in brackets
after .SpecialCells mean all cells.
Thisworkbook.Worksheets("Sheet1").UsedRange.Specia lCells(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

--

Dave Peterson