Looping through columns

R

RLN

Re: 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 ***
 
D

Dave Peterson

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.numberformat = "@"
.range("j1:k1").entirecolumn.numberformat = "0"
end with
next wks

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top