Iterate columns
 

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.

Iterate columns
 

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


"wired" wrote in message
...
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.

Iterate columns
 

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

--
Regards,
Tom Ogilvy

"wired" wrote in message
...
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.

Iterate columns
 

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


"wired" wrote in message
...
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.

Iterate columns
 

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

--
Vasant


"wired" wrote in message
...
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.

Iterate columns
 

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?

-----Original Message-----
Dim cell as Range
for each cell in Activesheet.UsedRange
' debug.print cell.address
Next

--
Regards,
Tom Ogilvy

"wired" wrote in
message
...
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.


.

Iterate columns
 

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?

-----Original Message-----
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


"wired" wrote in
message
...
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.


.

Iterate columns
 

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

"wired" wrote in message
...
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?

-----Original Message-----
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


"wired" wrote in
message
...
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.


.

Iterate columns
 

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

"wired" wrote in
message
...
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?

-----Original Message-----
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


"wired" wrote in
message
...
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.


.



.

Iterate columns
 

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

Alan Beban

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


-----Original Message-----
Dim cell as Range
for each cell in Activesheet.UsedRange
' debug.print cell.address
Next

--
Regards,
Tom Ogilvy

"wired" wrote in

message

...

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.


.