Selecting cols for deletion

[agibsonsw]

Hello. Excel 2007 VBA.

Is there a neat way to select a number of columns for deletion or formatting? Something like

Range("A:A,C:E,G:G").EntireColumn, looks messy.

Is there a similar way to this which uses column numbers? Andy.

[HansV]

I don't know of a really elegant way to do this. If you would prefer to use column numbers, you can use something like

Code: Select all

Union(Columns(1), Columns(3), Columns(6), Columns(10), Columns(15), Columns(21))

or loop through an array:

Code: Select all

Sub LoopColumns()
  Dim c
  For Each c In Array(1, 3, 6, 10, 15, 21)
    ' Do something with Columns(c) here
  Next c
End Sub

[StuartR]

If you are going to loop through an array then be careful about deleting columns, as this will move subsequent columns to the left and you could end up deleting the wrong ones. The easiest solution to this is to start at the highest column number and work downwards.

[HansV]

Good point! (Also see Looping backwards to delete items)

[agibsonsw]

Thank you both.

Adapting an example to use range names gives

Union(Columns(range("Grade").Column),Columns(range("Office").Column)).Select

which is (again) not "beautiful" but flexible. Ta, Andy.

[Rick Rothstein]

Hello. Excel 2007 VBA.

Is there a neat way to select a number of columns for deletion or formatting? Something like

Range("A:A,C:E,G:G").EntireColumn, looks messy.

Is there a similar way to this which uses column numbers? Andy.

Personally, I think using column letters is far easier to work with then using column numbers, especially for the columns way to the right. I'm not sure whether this qualifies as less "messy" than what you posted, but as an alternative to it, you could use Range("A1,C:E,G1").EntireColumn instead.