Macro deleting 2 columns instead of 1

[mkron]

Hello,

I had a working macro but today it started giving me an error. When formatting the data, it is deleting 2 columns when it is to be deleting 1. I am not sure why it is doing this. Any tips or suggestions would be greatly appreciated.

Below is the section of code where it is doing this.

    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft

Thanks,
MK

 

[Rick Rothstein]

Hello,

I had a working macro but today it started giving me an error. When formatting the data, it is deleting 2 columns when it is to be deleting 1. I am not sure why it is doing this. Any tips or suggestions would be greatly appreciated.

Below is the section of code where it is doing this.

    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft

Your code only deletes one column for each selection/delete combination above. You might be interested in knowing that all of the code lines you posted above can be replaced by this single line of code...

[table="width: 500"]
[tr]
	[td]Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W").EntireColumn.Delete[/td]
[/tr]
[/table]

 

[mkron]

Your code only deletes one column for each selection/delete combination above. You might be interested in knowing that all of the code lines you posted above can be replaced by this single line of code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W").EntireColumn.Delete[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Rick! I did not know that and it is very useful and shrinks my code alot.

Unfortunately, it still failed when it got to that line of code and gave me error:
Run-time error '1004':
Delete method of Range class failed

I forgot to mention and don't know if it makes a difference but the columns are all part of a table.

Thanks again,
MK

 

[Rick Rothstein]

Thanks Rick! I did not know that and it is very useful and shrinks my code alot.

Unfortunately, it still failed when it got to that line of code and gave me error:
Run-time error '1004':
Delete method of Range class failed

I forgot to mention and don't know if it makes a difference but the columns are all part of a table.

So you only want to delete the columns within the table then, correct? What is the name of your table and what range of cells does it cover?

 

[mkron]

So you only want to delete the columns within the table then, correct? What is the name of your table and what range of cells does it cover?

It can delete the entire column and not just the table. I didn't know if it would affect the code or not.
It is named Table1.

 

[Rick Rothstein]

It can delete the entire column and not just the table. I didn't know if it would affect the code or not.
It is named Table1.

Apparently, when a table is involved, you have to delete the columns one at a time, so the shortcut I posted won't work for you. Try this macro instead...

Sub DeleteColumnsIntersectingTable()
  Dim X As Long, ColsToDelete As Variant
  ColsToDelete = Split("C E G I K M O Q S U W")
  Application.ScreenUpdating = False
  For X = UBound(ColsToDelete) To 0 Step -1
    Columns(ColsToDelete(X)).Delete
  Next
  Application.ScreenUpdating = True
End Sub