Error 1004 - VBA Excel - delete column

Question

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.

Answer 1

Understand?
I think your monster range expression is well outside of what is expected. It also has A LOT of redundant columns: adjacent ones can be handled using colFirst:colLast syntax like you did with K:O.
By trial and error I found that the string can be up to 255 chars, and yours is almost twice as much.

You could create an array of numeric column numbers, then loop over the elements of that array and delete them one at a time.

Answer 2

I see.

I have solved joy more column to delete in range instead one by one.