PDA

View Full Version : [SOLVED] Select Multiple Columns using Column Number



Cosmos75
12-01-2004, 01:07 PM
Is there a way to select multiple columns in VBA using the column number?

I have this


Range("D:E,G:H,J:K,M:N").Select
Selection.Delete Shift:=xlToLeft
But I would prefer to be able to select those columns using column numbers. It will not always be the same columns so using column numbers would be easier for me to code.

Cosmos75
12-01-2004, 04:55 PM
Sub delcols()
Dim x, y As Range, z As Integer
x = Array(2, 4, 6, 8, 10)
Set y = Columns(x(0))
For z = 1 To UBound(x)
Set y = Union(y, Columns(x(z)))
Next z
y.Select
Selection.Delete Shift:=xlToLeft
End Sub
Not my creation! See here (http://www.mrexcel.com/board2/viewtopic.php?p=575138).

Aaron Blood
12-02-2004, 10:27 AM
Thought you might also find this interesting...



coltxt = "C4,C5,C7,C8,C10,C11,C13,C14"
Range(Application.ConvertFormula(coltxt, xlR1C1, xlA1)).Delete



Range syntax is also acceptable: "C4:C5, C7:C8"

chirag050675
05-04-2018, 04:19 AM
Dear Sir Aaron Blood,

Just amazing , magical code...its very -very helpful to users.

Regards,

Chirag Raval

jolivanes
05-04-2018, 10:52 AM
Another way of attacking the problem.

Sub Or_Like This()
Dim Rng As Range
On Error GoTo Canceled
Set Rng = Application.InputBox(Prompt:="Select Columns With Mouse." & vbLf & "For Multiple Selections, hold "Ctrl" Button Down.", Title:="Select", Type:=8)
Rng.Columns.Delete
Canceled:
End Sub