Move to next line in VBA code

deb

Active Member
Joined
Feb 1, 2003
Messages
396
Code:
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...
Code:
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?
 
Last edited by a moderator:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
623
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Re: nove to next line in VBA code

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


Code:
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:

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

deb

Active Member
Joined
Feb 1, 2003
Messages
396
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

Well-known Member
Joined
Nov 16, 2013
Messages
623
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Re: nove to next line in VBA code

no problem, thanks for the feedback
 

deb

Active Member
Joined
Feb 1, 2003
Messages
396

ADVERTISEMENT

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

Well-known Member
Joined
Nov 16, 2013
Messages
623
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Re: nove to next line in VBA code

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

Code:
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
Re: nove to next line in VBA code

How about
Code:
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
 

Forum statistics

Threads
1,148,272
Messages
5,745,790
Members
423,973
Latest member
man_this_is_hard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top