Number Format on multiple Ranges

[Siyanna]

Hi Lovely people,

How can i amend this to change the format to multiple ranges selected

the below code aint working but i gave it a shot i gues

Ws.Range("D" & LastWsRow + 1,"F" & LastWsRow + 1,"H" & LastWsRow + 1,"J" & LastWsRow + 1,"L" & LastWsRow + 1,"N" & LastWsRow + 1 _
       "P" & LastWsRow + 1,"R" & LastWsRow + 1,"T" & LastWsRow + 1,"V" & LastWsRow + 1,"X" & LastWsRow + 1,"Z" & LastWsRow + 1,"AB" & LastWsRow + 1 _
       "AD" & LastWsRow + 1, "AF" & LastWsRow + 1,"AH" & LastWsRow + 1.NumberFormat = "$#,##0.00"

 

[jasonb75]

I don't think you can define non-contiguous ranges in that way, with my limited knowledge of vba I would go with something like

Dim mCol As Long
For mCol = 4 To 34 Step 2
    ws.Cells(LastWsRow + 1, mCol).NumberFormat = "$#,##0.00"
Next

But I expect there are better ways.

 

[hippiehacker]

don't know if better but its working

Dim MyArray()
Dim X
Dim c As String

MyArray = Array("A", "B", "C", "E") ' change the array to the columns you want

For X = 0 To UBound(MyArray) ' determine lenght of array and how long to loop

c = MyArray(X)
 Sheets("Sheet1").Range(c & "1" & ":" & c & Range(c & Rows.Count).End(xlUp).Row).NumberFormat = "\$#.##000"
Next X

 

[AlphaFrog]

Yet another method.

Range("D1, F1, H1, J1, L1, N1, P1, R1, T1, V1, X1, Z1, AB1, AD1, AF1, AH1").Offset(LastWsRow).NumberFormat = "$#,##0.00"

 

[Siyanna]

Thank You all

You guys are awesome

 

[Siyanna]

Yet another method.

Range("D1, F1, H1, J1, L1, N1, P1, R1, T1, V1, X1, Z1, AB1, AD1, AF1, AH1").Offset(LastWsRow).NumberFormat = "$#,##0.00"

Hi AlphaFrog,

I have tried to add more ranges but for some reason it bugs out. Am i missing something

With .Range("E5:E10,G5:G10,I5:I10,K5:K10,M5:M10,O5:O10,Q5:Q10,S5:S10,U5:U10,W5:W10,Y5:Y10,AA5:AA10,AC5:AC10,AE5:AE10,AG5:AG10,AI5:AI10,AM5:AM10,E29:E34,G29:G34,I29:I34,K29:K34,M29:M34,O29:O34,Q29:Q34,S29:S34,U29:U34,W29:W34,E65:E69,G65:G69,I65:I69,K65:K69,M65:M69,O65:O69,Q65:Q69,S65:S69,U65:U69,W65:W69,Y65:Y69,AA65:AA69,AC65:AC69")
        .NumberFormat = "$#,##0.00"
End with

 

[AlphaFrog]

I think there is a limit of 30 areas within a one Range reference, but I'm not positive. Maybe you should consider using a loop like jasonb75 suggested. Or you would have to split it up into multiple range references each with 30 or less areas.

 

[Siyanna]

Thank You ALPHAFROG

I am having this weird problem when i open my spreadsheet and enable macros. I am getting this dialogue box all the time (Not enough recources to display completely)

Anyone know how to get round this problem?