delete Multiple columns by VBA

Question

i Have 700 columns in a sheet. I want to delete more than 650 out of them. If I use range as A:W and more then I get 400 Error.

Is there any limit to no. of column deletion in VBA.

Can you add your code to the question? – Tim Stack Jun 19 '19 at 06:34 — Tim Stack, Jun 19 '19 at 06:34

score 0 · Answer 1 · edited Jun 19 '19 at 11:48

0

Loop through them from rightmost column and delete them one by one.

Sub test()
Dim c As Long, r As Long
    Worksheets(1).Activate
    'populate 700 columns
    For c = 1 To 700
        Cells(1, c).Value = "col " & c
        For r = 2 To 10
            Cells(r, c).Value = "hej " & c & "/" & r
        Next
    Next
    'remove e.g. even columns
    For c = 700 To 1 Step -2
        Columns(c).Select
        Selection.Delete Shift:=xlToLeft
    Next
End Sub

edited Jun 19 '19 at 11:48

Tim Stack

3,209
3
18
39

answered Jun 19 '19 at 10:18

Jörgen R

366
1
10

This method uses `Select` and `Activate` which is bad practice – Tim Stack Jun 19 '19 at 10:21
Why is it bad practice @Tim-Stack? – Jörgen R Jun 20 '19 at 12:06
It is asking for errors, which will pop up very soon. Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Tim Stack Jun 20 '19 at 12:49
If you would allow I could add a version of your code converted not to use `Select` and `Activate` to your answer – Tim Stack Jun 20 '19 at 12:52

delete Multiple columns by VBA

1 Answers1