Iterate columns

  • Thread starter Thread starter wired
  • Start date Start date
W

wired

Hello.

I need some guidance here. I want to dimension a variable
and use it in a "For Each" structure to iterate through
the "used range" in a sheet. I'm using "Option Explicit".

In this structure the variable has to be an object or
variant. Since there is no "Column" object already
defined, what should I use that would be most efficient
and need the least overhead? i.e. Should I just "Dim as
Variant" or something else?

Your example code would be most appreciated. Thanks in
advance.
 
Wired

one way:

Sub IterateThroughRange()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
Debug.Print Cell.Address
Next 'Cell
End Sub

Regards

Trevor
 
Dim cell as Range
for each cell in Activesheet.UsedRange
' debug.print cell.address
Next
 
Try something like

Dim Rng As Range
For Each Rng In Worksheets("Sheet1").UsedRange
Debug.Print Rng.Address
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
I'm not sure why you would want to use a column as the base unit, but try
something like this:

Dim i As Integer, colCount As Integer, rngTest As Range
With ActiveSheet.UsedRange
ColCount = .Columns.Count
For i = 1 To ColCount
Set rngTest = .Columns(i)
'Do whatever you want to do with the range rngTest
Next
End With
 
Thanks, but, I don't want to loop through each cell...
I want to perform an action on each entire column.

What do I do in that case?
 
Thanks, but I don't want to loop through each CELL. I want
to perform an action on each entire column.

What would you suggest?
 
Try

Dim Col As Range
For Each Col In ActiveSheet.UsedRange.Columns
Debug.Print Col.Address
Next Col

or

Dim Col As Range
For Each Col In ActiveSheet.UsedRange.Columns
Debug.Print Col.EntireColumn.Address
Next Col


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Just what I needed. Thanks much.
-----Original Message-----
Try

Dim Col As Range
For Each Col In ActiveSheet.UsedRange.Columns
Debug.Print Col.Address
Next Col

or

Dim Col As Range
For Each Col In ActiveSheet.UsedRange.Columns
Debug.Print Col.EntireColumn.Address
Next Col


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




.
Click to expand...
 
Back
Top