Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear UsedRange except specific cells? | Excel Programming | |||
USEDRANGE | Excel Programming | |||
Help - loop through cells in a range that are not together (several different cells as Target) | Excel Programming | |||
UsedRange & Formatting of Cells | Excel Programming | |||
Using UsedRange as limits in a For Each loop but for cells on another sheet ? | Excel Programming |