Looping through columns
 

Excel 2003

Hello.
I have this code from a macro and am looking to streamline it:

<begin macro
Columns("A:A").Select
Selection.NumberFormat = "General"

Columns("B:B").Select
Selection.NumberFormat = "General"

Columns("C:C").Select
Selection.NumberFormat = "General"

Columns("D:D").Select
Selection.NumberFormat = "@"

Columns("E:E").Select
Selection.NumberFormat = "@"

Columns("F:F").Select
Selection.NumberFormat = "General"

Columns("G:G").Select
Selection.NumberFormat = "@"

Columns("H:H").Select
Columns("I:I").Select
Selection.NumberFormat = "@"

Columns("J:J").Select
Selection.NumberFormat = "0"

Columns("K:K").Select
Selection.NumberFormat = "0"
<end macro

I am not sure how to set up code to loop through an .XLS file to get the
sheetname, count the valid columns and rows (column/row numbers can
change from month to month)
Then as I loop through each column if it is A,B,C, or F then set
numberformat to "General", etc.....

Do I have to invoke the Excel Object -and- the Workbook object, -and-
the sheet object? This is where I get confused.

Thnx....


*** Sent via Developersdex http://www.developersdex.com ***

Looping through columns
 

I'm not sure I understand what you're doing, but if you wanted to loop through
all the worksheets in the activeworkbook, you could do something like:

Dim Wks as worksheet
for each wks in activeworkbook.worksheets
with wks
.range("a1:c1,f1").entirecolumn.numberformat = "General"
.Range("d1:E1,g1,i1,j1,k1").entirecolumn.numberfor mat = "@"
.range("j1:k1").entirecolumn.numberformat = "0"
end with
next wks

I was confused about what happens with column H, though.

RLN wrote:

Excel 2003

Hello.
I have this code from a macro and am looking to streamline it:

<begin macro
Columns("A:A").Select
Selection.NumberFormat = "General"

Columns("B:B").Select
Selection.NumberFormat = "General"

Columns("C:C").Select
Selection.NumberFormat = "General"

Columns("D:D").Select
Selection.NumberFormat = "@"

Columns("E:E").Select
Selection.NumberFormat = "@"

Columns("F:F").Select
Selection.NumberFormat = "General"

Columns("G:G").Select
Selection.NumberFormat = "@"

Columns("H:H").Select
Columns("I:I").Select
Selection.NumberFormat = "@"

Columns("J:J").Select
Selection.NumberFormat = "0"

Columns("K:K").Select
Selection.NumberFormat = "0"
<end macro

I am not sure how to set up code to loop through an .XLS file to get the
sheetname, count the valid columns and rows (column/row numbers can
change from month to month)
Then as I loop through each column if it is A,B,C, or F then set
numberformat to "General", etc.....

Do I have to invoke the Excel Object -and- the Workbook object, -and-
the sheet object? This is where I get confused.

Thnx....

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Looping through columns
 

Your solution worked great, Dave, thank you! I appreciate your help
very much.

RLN


*** Sent via Developersdex http://www.developersdex.com ***