error 1004 - delete column

[LCC]

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.

 

[jasonb75]

The error is likely because the string is too long, I believe that it is limited to 255 characters. Where you have consecutive columns, try using A:C,E:F, etc instead.

If it still errors after that, then you would need to break it down into multiple parts. If you do that it will be better to start from the right side of the sheet, otherwise it is likely that the wrong columns will be deleted by the later lines of code.

 

[LCC]

The error is likely because the string is too long, I believe that it is limited to 255 characters. Where you have consecutive columns, try using A:C,E:F, etc instead.

If it still errors after that, then you would need to break it down into multiple parts. If you do that it will be better to start from the right side of the sheet, otherwise it is likely that the wrong columns will be deleted by the later lines of code.

great stuff.
I will try to combine the lines instead one by one.
I will let you know the results soon.

One more question. I cannot increase the limit of 255 to a bigger one?

 

[jasonb75]

I cannot increase the limit of 255 to a bigger one?

There is no way of increasing this, or any similar limit within excel / vba.

 

[LCC]

There is no way of increasing this, or any similar limit within excel / vba.

I see.
Tks.

 

[Michael M]

If there is a criteria for deleting said columns, you could Loop through them....starting from the right and working left !!

 

[Akuini]

One more question. I cannot increase the limit of 255 to a bigger one?

Try a workaround:

1. Put the range address in a string variable, it can be more than 256 characters.
2. Use split & Union to get the range

something like this:

Sub a1167744a()
Dim rng As Range
Dim x, arr
Dim txt As String

txt = ("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")

arr = Split(txt, ",")

    For Each x In arr
        If Not rng Is Nothing Then
            Set rng = Union(rng, Range(x))
        Else
            Set rng = Range(x)
        End If
    Next

rng.EntireColumn.Delete

End Sub

 

[LCC]

This has been solved my case once the Excel, there are too much useless column that's no necessary:

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

I mean, simply and just a line.
I will jump now to the next step guys.
Thank you very much for the ideas and assistance.