Iterate columns

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.
 
T

Trevor Shuttleworth

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
 
T

Tom Ogilvy

Dim cell as Range
for each cell in Activesheet.UsedRange
' debug.print cell.address
Next
 
C

Chip Pearson

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
 
V

Vasant Nanavati

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
 
W

wired

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?
 
W

wired

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?
 
C

Chip Pearson

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
 
W

wired

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...
 
A

Alan Beban

Each entire column? Or that portion of each column that is within the range?

Alan Beban
 
Top