error 1004 - VBA Delete column

[Canguini]

Hello team,


I'm not good enough with VBA and I would like your assistance to understand the VBA code below that always get an error 1004.
I have checked and sheet's name is correct, etc. if I add just very few columns, works perfectly.


For example, this code works well:


Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Sheets("Sheet1").Range("A:A,C:C,H:H,K:O,Q:U").EntireColumn.Delete
End Sub


But If I add many columns as per below, I get the error 1004:


Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Sheets("Sheet1").Range("A:A,B:B,C:C,E:E,F:F,H:H,J:J,K:K,L:L,M:M,N:N,O:O,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W ,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG,AH:AH,AI:AI,AK:AK,AL :AL,AN:AN,AO:AO,AP:AP,AQ:AQ,AR:AR,AS:AS,AT:AT,AU:AU,AV:AV,AW:AW,AX:AX,AY:AY ,AZ:AZ,BA:BA,BB:BB,BC:BC,BD:BD,BE:BE,BF:BF,BG:BG,BH:BH,BI:BI,BJ:BJ,BK:BK,BL :BL,BM:BM,BN:BN,BO:BO,BP:BP,BQ:BQ,BR:BR,BS:BS,BT:BT,BU:BU,BV:BV,BW:BW,BX:BX ,BY:BY,BZ:BZ,CA:CA,CB:CB,CD:CD,CE:CE,CF:CF,CG:CG,CH:CH,CI:CI,CJ:CJ,CK:CK,CL :CL,CM:CM,CN:CN,CO:CO,CP:CP,CQ:CQ,CR:CR,CS:CS,CT:CT,CU:CU,CV:CV,CW:CW,CX:CX").EntireColumn.Delete
End Sub


I much appreciate if you can assist me.


Thank you in advance.

[p45cal]

It doesn't like the long strings within quote marks to be longer than 255 characters.
Two things you can do:
1. Break that string up into shorter lengths and execute multiple statements. To do this most robustly, delete the rightmost columns first because if you delete the left ones first, the right ones change their names! so:

Sheets("Sheet1").Range("BH:BH,BI:BI,BJ:BJ,BK:BK,BL:BL,BM:BM,BN:BN,BO:BO,BP:BP,BQ:BQ,BR:BR,BS:BS,BT: BT,BU:BU,BV:BV,BW:BW,BX:BX,BY:BY,BZ:BZ,CA:CA,CB:CB,CD:CD,CE:CE,CF:CF,CG:CG, CH:CH,CI:CI,CJ:CJ,CK:CK,CL:CL,CM:CM,CN:CN,CO:CO,CP:CP,CQ:CQ,CR:CR,CS:CS,CT: CT,CU:CU,CV:CV,CW:CW,CX:CX").EntireColumn.Delete
Sheets("Sheet1").Range("H:H,J:J,K:K,L:L,M:M,N:N,O:O,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,A B:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG,AH:AH,AI:AI,AK:AK,AL:AL,AN:AN,AO:AO,AP:A P,AQ:AQ,AR:AR,AS:AS,AT:AT,AU:AU,AV:AV,AW:AW,AX:AX,AY:AY,AZ:AZ,BA:BA,BB:BB,B C:BC,BD:BD,BE:BE,BF:BF,BG:BG").EntireColumn.Delete
Sheets("Sheet1").Range("A:A,B:B,C:C,E:E,F:F").EntireColumn.Delete

2. Much easier in this case because you've got lots of columns next to each other that you're deleting, so for example:

Sheets("Sheet1").Range("A:A,B:B,C:C,D:D,E:E,F:F").EntireColumn.Deletecan be condensed to:
Sheets("Sheet1").Range("A:F").EntireColumn.Delete
which means that your sample code can be condensed to:
Sheets("Sheet1").Range("A:C,E:F,H:H,J:O,Q:AI,AK:AL,AN:CX").EntireColumn.Delete

[Canguini]

It doesn't like the long strings within quote marks to be longer than 255 characters.
Two things you can do:
1. Break that string up into shorter lengths and execute multiple statements. To do this most robustly, delete the rightmost columns first because if you delete the left ones first, the right ones change their names! so:

Sheets("Sheet1").Range("BH:BH,BI:BI,BJ:BJ,BK:BK,BL:BL,BM:BM,BN:BN,BO:BO,BP:BP,BQ:BQ,BR:BR,BS:BS,BT: BT,BU:BU,BV:BV,BW:BW,BX:BX,BY:BY,BZ:BZ,CA:CA,CB:CB,CD:CD,CE:CE,CF:CF,CG:CG, CH:CH,CI:CI,CJ:CJ,CK:CK,CL:CL,CM:CM,CN:CN,CO:CO,CP:CP,CQ:CQ,CR:CR,CS:CS,CT: CT,CU:CU,CV:CV,CW:CW,CX:CX").EntireColumn.Delete
Sheets("Sheet1").Range("H:H,J:J,K:K,L:L,M:M,N:N,O:O,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,A B:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG,AH:AH,AI:AI,AK:AK,AL:AL,AN:AN,AO:AO,AP:A P,AQ:AQ,AR:AR,AS:AS,AT:AT,AU:AU,AV:AV,AW:AW,AX:AX,AY:AY,AZ:AZ,BA:BA,BB:BB,B C:BC,BD:BD,BE:BE,BF:BF,BG:BG").EntireColumn.Delete
Sheets("Sheet1").Range("A:A,B:B,C:C,E:E,F:F").EntireColumn.Delete

2. Much easier in this case because you've got lots of columns next to each other that you're deleting, so for example:

Sheets("Sheet1").Range("A:A,B:B,C:C,D:D,E:E,F:F").EntireColumn.Deletecan be condensed to:
Sheets("Sheet1").Range("A:F").EntireColumn.Delete
which means that your sample code can be condensed to:
Sheets("Sheet1").Range("A:C,E:F,H:H,J:O,Q:AI,AK:AL,AN:CX").EntireColumn.Delete



Thank you very much mate.
I have solved the problem like this: (using range)

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Sheets("Sheet1").Range("A:B,D:E,G:G,I:N,P:AH,AJ:AK,AM:CA,CC:CU").EntireColumn.Delete
End Sub

The rest, in order to automatize the process, I will perform step by step.

[SamT]

Yet another method, which might be easier to maintain

Sub sbVBS_To_Delete_Specific_Multiple_Columns(ColsToDelete As Variant)
Dim i as long
With Sheets("Sheet1")
For i = Ubound(ColsToDelete) to LBound(ColsToDelete) Step -1 'Delete from Right to left
.Cells(1, ColsToDelete(i)).EntireColumn.Delete
Next i
End With


Sub DeleteThem_1()
Dim ColsToDelete As Variant
ColsToDelete = Array("A", "C", "H", "J", "K", "L","M", "N", "O", "Q", "R", "S", "T", "U")

bVBS_To_Delete_Specific_Multiple_Columns ColsToDelete
End Sub

Edit the ColsToDelete array and ...
Sub DeleteThem_2()
Dim ColsToDelete As Variant
ColsToDelete = Array("A", "B", "C", "E", "F", "H", "J", "K", "L", Etc)

bVBS_To_Delete_Specific_Multiple_Columns ColsToDelete
End Sub
Use as many Subs DeleteThem_N as needed

[Canguini]

Nice one as well.
I will try use this in another case that may be better :)

Tk all for the assistance