CODEWITHSUNDEEP
excelvbaformat
duke
duke

Reputation: 135

excel vba: Numberformat for a list of Columns

is there a shorter way for using numberFormaton a list of columns? e.g. I have

.Columns("C").NumberFormat = "#,##0.00_)"
.Columns("F").NumberFormat = "#,##0.00_)"
.Columns("G").NumberFormat = "#,##0.00_)"

something like (which does not work)

 Range(.Columns(3), .Columns(6), .Columns(9)).NumberFormat = "#,##0.00_)"

thanks for helping, dukehh

Upvotes: 0

Views: 559

Answers (3)

storm_88
storm_88

Reputation: 92

another option

Dim col
For Each col In Array("A", "B", "C")
    .Columns(col).NumberFormat = "#,##0.00_)"
Next col

but union is much faster

Upvotes: 0

duke
duke

Reputation: 135

I used Nathan_Sav's (thankU) solution like so:

   Dim r As Excel.Range
   Set r = Union(.Columns(3), .Columns(6), .Columns(9))
   r.NumberFormat = "#,##0.00_)"

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

Something like so, could work for you.

Dim r As Excel.Range

Set r = Union(ActiveSheet.Columns(1), ActiveSheet.Columns(3), ActiveSheet.Columns(5))
r.Select

Upvotes: 2

Related Questions