How to delete multiple columns in Excel VBA?

Question

How to delete multiple columns in Excel VBA? I tried:

Sub DelColumns()
  Dim col As Range

  For Each col In Range("A:C,E:E,H:S,U:AK,AM:AM,AO:AU,BC:BI,BK:BV").Columns
    col.EntireColumn.Delete
  Next col

End Sub

Update. I try to do it on a table which is a show-detail table of pivot table. Is it possible to delete table columns without converting the table to a range first?

I get a msg Run time error 1004 Delete method of range class failed — Przemyslaw Remin
– Przemyslaw Remin, Commented Oct 1, 2015 at 8:55
Then something else is a problem - the code above works for me. Are there merged cells in your spreadsheet that might be blocking the deletion? — Phylogenesis
– Phylogenesis, Commented Oct 1, 2015 at 9:00
No merge cells. I use the code on a table which is shown after double clicking on a pivot table (show details). I have many columns then which I do not need. — Przemyslaw Remin
– Przemyslaw Remin, Commented Oct 1, 2015 at 9:02

Shalvin Abraham · Accepted Answer · 2015-10-01 08:47:23Z

9

You can use the delete method against Range to delete columns.

In your case,

Sub DelColumns()

  Range("A:C,E:E,H:S,U:AK,AM:AM,AO:AU,BC:BI,BK:BV").Delete

End Sub

answered Oct 1, 2015 at 8:47

Shalvin Abraham

4583 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Przemyslaw Remin Over a year ago

I get a msg Run time error 1004 Delete method of range class failed

Shalvin Abraham Over a year ago

I just executed same code here and it is working fine. Can you please check with one or two ranges and see if any errors coming?

Collectives™ on Stack Overflow

How to delete multiple columns in Excel VBA?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related