| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SOLVER does not iterate / work | Excel Discussion (Misc queries) | |||
| How can I iterate numbering when printing | Excel Discussion (Misc queries) | |||
| Iterate Circular Reference | Excel Discussion (Misc queries) | |||
| Iterate though all open spreadsheets | Excel Worksheet Functions | |||
| How do I fill down formulas so they iterate in intervals other th. | Excel Worksheet Functions | |||
Linear Mode
