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:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
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
622
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
622
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
47,904
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,656
Members
410,566
Latest member
Jonniehoffman
Top