Move to next line in VBA code

[deb]

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
     Sheets("2_2").Range("R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ,BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP,BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ,CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ,DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ").Delete
End Sub

Trying to move to the next line using the below...

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
     Sheets("2_2").Range("R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ, _
BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP,BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ, _
CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ, _
DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ").Delete
End Sub

I get a Compiler error: Expected list separator or )

What am I doing wrong?

 

[cooper645]

Re: nove to next line in VBA code

you need to join the ranges on each line like so:

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
     Sheets("2_2").Range("R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ," & _
     "BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP,BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ," & _
     "CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ," & _
     "DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ").Delete
End Sub

but if your just deleting every other column in that range you could shorten it like so:

Sub DelEvenColRtoDJ()Dim i As Integer
For i = 114 To 18 Step -2
    Columns(i).EntireColumn.Delete
Next
End Sub

 

[deb]

Re: nove to next line in VBA code

This is perfect. Thank you.

FYI it is mostly every other. then moves to every third then every other again.
nothing is ever simple!!

 

[cooper645]

Re: nove to next line in VBA code

no problem, thanks for the feedback

 

[deb]

Re: nove to next line in VBA code

when I run the code I get runtime error 1004
Application defined or object defined error
Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Sheets("2_2").Range("R:R,T:T,V:V,X:X,Z:Z,AB:AB,AD:AD,AF:AF,AH:AH,AJ:AJ,AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ," & _
"BB:BB,BD:BD,BF:BF,BH:BH,BJ:BJ,BL:BL,BN:BN,BP:BP,BR:BR,BT:BT,BV:BV,BX:BX,BZ:BZ," & _
"CB:CB,CD:CD,CF:CF,CH:CH,CJ:CJ,CL:CL,CN:CN,CP:CP,CR:CR,CT:CT,CV:CV,CX:CX,CZ:CZ," & _
"DB:DB,DD:DD,DF:DF,DH:DH,DJ:DJ").Delete
End Sub

 

[cooper645]

Re: nove to next line in VBA code

I tried to create your range as an array, and excel has a memory issue:

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Dim myarray As Variant
myarray = ("18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56,58,60,62,64,66,68,70,72,74,76,78,80,82,84,86,88,100,102,104,106,108,110,112,114")
Sheets("2_2").Columns(myarray).EntireColumn.Delete
End Sub

How many rows are being used?

and are the columns to be deleted always the same?

If so give me another example of the column range that needs deleting and I will try and sort a macro for you.

 

[Fluff]

Re: nove to next line in VBA code

How about

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Dim myarray As Variant
myarray = ("R1,T1,V1,X1,Z1,AB1,AD1,AF1,AH1,AJ1,AL1,AN1,AP1,AR1,AT1,AV1,AX1,AZ1,BB1,BD1,BF1,BH1,BJ1,BL1,BN1,BP1,BR1,BT1,BV1,BX1,BZ1,CB1,CD1,CF1,CH1,CJ1,CL1,CN1,CP1,CR1,CT1,CV1,CX1,CZ1,DB1,DD1,DF1,DH1,DJ1")
Sheets("2_2").Range(myarray).EntireColumn.Delete
End Sub