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.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
--
Dave Peterson
|
Similar Threads
