question

LuizCanguini-5423 avatar image
0 Votes"
LuizCanguini-5423 asked LuizCanguini-5423 answered

Error 1004 - VBA Excel - delete column

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.

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LuizCanguini-5423 avatar image
0 Votes"
LuizCanguini-5423 answered

I see.

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout avatar image
0 Votes"
TvanStiphout answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.