VBA - Select Multiple Columns using Column Number

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
Is there a way to select multiple columns in VBA using the column number?

I have this
Code:
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi Cosmos75,

It is unfortunately a bit verbose, but I believe still the simplest way:

Union(Columns(14), Columns(13), Columns(11), _
Columns(10), Columns(8), Columns(7), _
Columns(5), Columns(4)).Delete Shift:=xlToLeft

Damon
 
Upvote 0
Damon,

Thank you for your reply!

That example works great if I know in advance the number of columns I wanted to delete. Theoretically, I would always delete the same number of columns but I just want to cover the oddd chance that I may have to delete more (or less) than that particular number of columns.

I wonder if I can add to Union() via a loop of some sort?

That at least gives me a starting point.
THANK YOU!
 
Upvote 0
Perhaps something like this:

Code:
Sub delcols()
Dim x, y As Range, z As Integer
x = Array(14, 13, 11, 10, 8, 7, 5, 4)
Set y = Columns(x(0))
For z = 1 To UBound(x)
Set y = Union(y, Columns(x(z)))
Next z
y.Delete Shift:=xlToLeft
End Sub
 
Upvote 0
HOTPEPPER,

Wonderful!

I was trying to use and array and add it back into the Union but had no idea what the syntax should be.
Code:
Set y = Union(y, Columns(x(z)))
That's what I was missing! Not that I'd ever figure that out on my own!

THANK YOU!
 
Upvote 0
Hi all,

I just want to understand how many columns can I input in the Array.
x = Array(14, 13, 11, 10, 8, 7, 5, 4)


I was using the following and its not working:

x = Array(2,3,9,10,12,13,16,17,18,20,21,22,23,24,25,26,27,3,29,30,31,30,33,34,35,37,49,50,51,52,53,54,55,5,57,58,59,60,61,82,83,84,97,98,99,100,101,102,103,104,105,106)

Actually, I was using a variable to pick the required column number and passing it in the above argument..something as below

X = Array(My_Array) where My_Array = 2,3,9,10,12,13,16,17,18,20,21,22,23,24,25,26,27,3,29,30,31,30,33,34,35,37,49,50,51,52,53,54,55,5,57,58,59,60,61,82,83,84,97,98,99,100,101,102,103,104,105,106


Kindly suggest.
 
Last edited:
Upvote 0
Pravin,
Excel doesn't like when the column reference number is greater than 26 (columns AA, AB etc).

So, if you are deleting columns past column Z, you will need to delete them 26 at a time.
For example: if you want to delete columns 24 to 29, you will write

Union(Columns(24), Columns(25), Columns(26)).Delete Shift:=xlToLeft
Union(Columns(24), Columns(25), Columns(26)).Delete Shift:=xlToLeft

Yes, it is the exact same line written twice. Note, Column 24 in line 2 is actually column 27. column 27 moved to column 24 when you deleted 24, 25 and 26 in the first line.

Good luck!
 
Upvote 0
I know this is old but for anyone else who may come across this thread, a much easier way to do this would be to do the following:

Dim x as Integer
Dim y as Integer

x = 1 ' For A
y = 4 ' For D

Columns(Chr(64 + x) & ":" & Chr(64 + y)).Select

This would select columns A to D.
using this format will allow you to easily select any range of columns from A to Z using completely variable values. I am sure with a bit of creativity you could also write a function that would enable selections of any range of columns past Z. (ie IF x > 26 then ...)
 
Upvote 0
Adding this as it was the first result of my google search, despite the age:

The following function will convert a column number into its corresponding column string.
Code:
Function CNTS(intCol As Integer) As String    
    CNTS = Cells(1, intCol).Address(1, 0)
    CNTS = Left(CNTS, InStr(CNTS, "$") - 1)
End Function

No error checking, but it will work for any valid column number. You can use something similar to convert an entire array into a valid address with a little work. You could even have it read the array, re-order the elements ascending and group sequential numbers into a single reference (IE: Passing 1, 3, 5, 2, 12 to return A:C,E,L)

A little more detailed than I have time to go into now, but it's possible.
 
Upvote 0
Is there a way to select multiple columns in VBA using the column number?
Yes, there is...and from this thread there are multiple ways. Here is what I use for a contiguous set of Columns, from the first one to delete to the last.

ws.Range(Cells(1, 2), Cells(ws.Rows.count, ws.UsedRange.Columns.count)).Delete

If you want to use and array to do the deletions I would suggest something like

Type ColumnRange
LeftColNum as Integer
RightColNum as Integer
Type End

Dim ColsToDelete() as ColumnRange

Redim ColsToDelete(<Number of Ranges>)

<Load ColsToDelete>
<Sort ColsToDelete descending>
For i = Lbound(ColsToDelete) to Ubound(ColsToDelete)
ws.Range(Cells(1, ColsToDelete(i).LeftColNum ), Cells(ws.Rows.count, ColsToDelete(i).RightColNum).Delete​
Next i
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top