Problem with deleting multiple columns in VBA

[MrKowz]

Ok, another problem. I need to delete very specific columns every month from the reports I open, but when I run the macro that is supposed to do that, it deletes almost everything from the report, even the columns I did not tell the macro to select.

Do I need to add in something like "Range("A1").Select" in between each of the Selection.Delete Shift:=xlToLeft?

    Columns("C:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:S").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:W").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:AA").Select
    Selection.Delete Shift:=xlToLeft
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("O:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("P:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("Q:AE").Select
    Selection.Delete Shift:=xlToLeft
    Columns("R:R").Select
    Selection.Delete Shift:=xlToLeft
    Columns("S:T").Select
    Selection.Delete Shift:=xlToLeft
    Columns("T:T").Select
    Selection.Delete Shift:=xlToLeft
    Columns("U:AG").Select
    Selection.Delete Shift:=xlToLeft
    Columns("V:V").Select
    Selection.Delete Shift:=xlToLeft
    Columns("W:X").Select
    Selection.Delete Shift:=xlToLeft
    Columns("X:X").Select
    Selection.Delete Shift:=xlToLeft
    Columns("Y:AM").Select
    Selection.Delete Shift:=xlToLeft
    Columns("Z:Z").Select

The reason this code looks so nasty is because when I was selecting multiple columns with something like "Columns("A:Z,AB:AC,AF:AF").Select, I was getting an overlap error.

 

[Jonmo1]

Welcome to the board...

You should try to not select at all...

It can probably all be done in one line, adjust and add columns as needed...

Range("C:P,R:R,U:V,Z:AC").EntireColumn.Delete

Hope this helps..

 

[MrKowz]

Genius!

 

[CLEO87]

Hi there i was having the same issue as mentioned above but when i pop this code in:

 

[Chris K]

Hi Guys,

I'm having a similar problem. I used the code:

Range("C:E;G:K;N:S;W:AJ").EntireColumn.Delete

And

Columns("C:E;G:K;N:S;W:AJ").EntireColumn.Delete

And

Columns("C:E;G:K;N:S;W:AJ").EntireColumn.Select
Selection.EntireColumn.Delete

But there is an error. When they do work however, they don't delete the correct ones. Is there a way to delete the columns at the same time? I think it does the same thing as MrKowz's code. It deletes a range of colums then recalculates. eg. It recalcualtes, so after it deletes column B, it then starts again and what it now thinks is in Column D was originally in Column E.

Please help me understand how to do this.

Thank you

 

[Jonmo1]

VBA tends to be U.S. Centric...

Try with commas instead of semicolons.

 

[Chris K]

Hi Jonmo1,

I tried:

Sub Deletecolumns()


Columns("C:E,G:K,N:S,W:AJ").EntireColumn.Select
Selection.EntireColumn.Delete


End Sub

But it says incompatibility type.

Dy ou perhaps know why all of these codes that I'm trying won't work?

Thank you

 

[Jonmo1]

The first line you posted was correct, except for the ;

Range("C:E;G:K;N:S;W:AJ").EntireColumn.Delete

So just change the ; in that line to ,

 

[Chris K]

I tried

Sub Deletecolumns()
Range("C:E,G:K,N:S,W:AJ").Delete

End Sub

and it worked!!

Thanks for your time