currency format columns in listobject table

[ajm]

hi all, with the following snippet, i am attempting to reformat several columns to currency in the table i have created.

With Sheets("ITR")
            .ListObjects.Add(SourceType:=xlSrcRange, Source:=.Cells(1).CurrentRegion, _
                XlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleMedium2").Name = "TblITR"
            .Columns("U:Z", "AG", "AN:AU", "AZ:BA").Style = "Currency"
            .Columns("U:Z", "AG", "AN:AU", "AZ:BA").NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
            .Cells.EntireColumn.AutoFit
        End With

it falls over on this line - .Columns("U:Z", "AG", "AN:AU", "AZ:BA").Style = "Currency"-

where i try to change all the currency columns at the one time. Do i need to have a new line for each column band?

 

[Alex Blakenburg]

I don't believe the columns property works the way you want.

Either of these will work, if that helps:-

    Range("C:C,E:F").Style = "Currency"
    Range("c1,e1:f1").EntireColumn.Style = "Currency"

 

[ajm]

thanks alex. i got it to work after i removed the "" around each column(s) AND changed to .Range. The apostrophes still need to go around all the required columns, just not each set of them. Easier to see what i am trying to say.

Instead of: .Columns("U:Z", "AG", "AN:AU", "AZ:BA").Style = "Currency"

I used: .Range("U:Z, AG:AG, AN:AU, AZ:BA").Style = "Currency"

 

[Alex Blakenburg]

That was the first option I gave. I only gave the 2nd option in case you really hated having to do something like the AG:AG for single columns.
Glad you were able to get it to work. Thanks for letting me know.