VBA - Select Multiple Columns using Column Number

[Cosmos75]

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.

 

[vinu315]

Sorry to post on such an old thread, but I thought this to be quite useful in case you are looking at deleting continuous columns:

ActiveSheet.columns(8).Resize(ActiveSheet.Rows.Count, ActiveSheet.columns.Count - 8).Delete

 

[ryan0585]

Like everyone else who posted late, I feel compelled to. It's actually quite simple to do if you use the "EntireColumn" function on a range of cells:

Range(Cells(2, 14), Cells(2, 15)).EntireColumn.Select

This will select columns 14 and 15 (i.e. columns N and O). Good for contiguous columns, and allows you to replace the "14" and "15" with variables if needed.

 

[chirag050675]

Perhaps something like this:

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

Dear Sir Scott Huish,

Amazing Code, Really Great...But can it be modify that it select only some ranges' (either range set as object or named range's columns) instead of entire column of sheet?

for example if my stetted range is "A1 to W500", then how this code select non contiguous columns of this range only and not entire columns of sheet?


Hope there are some solution found.


Regards,

Chirag Raval

 

[Peter_SSs]

Dear Sir Scott Huish,

Amazing Code, Really Great...But can it be modify that it select only some ranges' (either range set as object or named range's columns) instead of entire column of sheet?

for example if my stetted range is "A1 to W500", then how this code select non contiguous columns of this range only and not entire columns of sheet?


Hope there are some solution found.


Regards,

Chirag Raval

Is this the sort of thing you mean? This will delete the first 500 rows of columns A, E:G and T and move everything left.
Test in a copy of your workbook.

If it isn't what you meant, please try to explain more clearly and/or give examples.

Sub DeletePartialColumns()
  Intersect(Rows("1:500"), Range("A:A, E:G, T:T")).Delete Shift:=xlToLeft
End Sub

 

[chirag050675]

Is this the sort of thing you mean? This will delete the first 500 rows of columns A, E:G and T and move everything left.
Test in a copy of your workbook.

If it isn't what you meant, please try to explain more clearly and/or give examples.

Sub DeletePartialColumns()
  Intersect(Rows("1:500"), Range("A:A, E:G, T:T")).Delete Shift:=xlToLeft
End Sub

Dear Sir @Peter_SSs

Thank you very much for your kind help..

Yes another smart way you teach us,.
your code just do as I wish but...can it work on dynamic row instead of fixed row reference ?
I modify your code to just select/ or process something like below I apply "Bold" to desired columns.

Sub ProcessPartialColumns()
  Intersect(Rows("1:500"), Range("A:A, E:G, T:T")).Font.Bold = True
End Sub

Can I use your valuable approach as below as use of LastRow Variable instead of fixed & hard coded rows numbers reference?

myrng.Intersect(Rows("1:LastRow"), Range("A:A, E:G, T:T")).Font.Bold = True



below is screen shot of my more precise requirement
[IMG]http://i64.tinypic.com/339irlu.jpg[/IMG]


Hope there are some solution found.

Regards,

Chirag Raval

 

[chirag050675]

Dear Sir @scott huish,

Amazing Code, Really Great...But can it be modify that it select only some ranges' (either range set as object or named range's columns) instead of entire column of sheet?

for example if my stetted range is "A1 to W500", then how this code select non contiguous columns of this range only and not entire columns of sheet?


Hope there are some solution found.


Regards,

Chirag Raval

Dear All Respected experts & Users,

Yes ...I modify code as I require & apply successful to my dynamic range stored in object variable.

here is successful modification of Mr @scott huise 's code.

Sub SelDeleteProcessNonContColmnsok()
Dim x, y As Range, z As Integer
Dim lastrow As Long
Dim myrng As Range
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myrng = .Range("A1:W" & lastrow)
With myrng
x = Array(14, 13, 11, 10, 8, 7, 5, 4)
Set y = myrng.Columns(x(0))
For z = 1 To UBound(x)
Set y = Union(y, myrng.Columns(x(z)))
Next z
'y.Delete Shift:=xlToLeft
y.Select
End With
End With

set myrng=nothing

End Sub

I just select this range as test ,but uses can do anything on columns of this dynamic range .

Hope This will be helpful.

Thank you very much for all expert's & user's for great help.

Regards,

Chirag Raval

 

[Peter_SSs]

your code just do as I wish but...can it work on dynamic row instead of fixed row reference ?
I modify your code to just select/ or process something like below I apply "Bold" to desired columns.

Sure, here's an example, assuming column A can be used to determine the last row

Sub WorkWithPartialColumns()
  With Intersect(Rows("1:" & Range("A" & Rows.Count).End(xlUp).Row), Range("A:A, E:G, T:T"))
    .Font.Bold = True
    .Interior.Color = vbBlue
  End With
End Sub

or if you want to use a LastRow variable

  With Intersect(Rows("1:" & LastRow), Range("A:A, E:G, T:T"))
    .Font.Bold = True
    .Interior.Color = vbBlue
  End With

 

[chirag050675]

Sure, here's an example, assuming column A can be used to determine the last row

Sub WorkWithPartialColumns()
  With Intersect(Rows("1:" & Range("A" & Rows.Count).End(xlUp).Row), Range("A:A, E:G, T:T"))
    .Font.Bold = True
    .Interior.Color = vbBlue
  End With
End Sub

or if you want to use a LastRow variable

  With Intersect(Rows("1:" & LastRow), Range("A:A, E:G, T:T"))
    .Font.Bold = True
    .Interior.Color = vbBlue
  End With

Dear Sir @Peter_SSs,

Amazing, Super , Magical

Your guideline just amazingly work on dynamic range also..

sorry to ask , if you don't mine , same process can be do with column no instead of columns name?
means use Range(1,7,9:11,17,22) instead of range("A:A, E:G, T:T"))?

Hope there are some solutions.

Again thank you for give your valuable time & effort.

Regards,

Chirag Raval

 

[Peter_SSs]

Not so easy with multiple disjoint columns using numbers. Depends a bit exactly what you have and what you are trying to do. Options would be to loop through the columns individually, or to use union like this

With Intersect(Rows("1:" & LastRow), Union(Columns(1), Columns(7), Columns(9).Resize(, 3), Columns(17), Columns(22)))

 

[chirag050675]

Not so easy with multiple disjoint columns using numbers. Depends a bit exactly what you have and what you are trying to do. Options would be to loop through the columns individually, or to use union like this

With Intersect(Rows("1:" & LastRow), Union(Columns(1), Columns(7), Columns(9).Resize(, 3), Columns(17), Columns(22)))

Dear Sir @Peter_SSs

Just Great , Amazing,, Again Super Shot,
Though it is pure one liner code, & if we don't mine to just type little long as ,

With Intersect(Rows("1:" & LastRow), Union(Columns(1), Columns(7), Columns(9).Resize(, 3), Columns(17), Columns(22)))

in short we can use "column number" in this approach.

And yes, if we do not hesitate for "Options would be to loop through the columns individually"
that definitely will also be very useful approach towards process of columns of range , with column numbers.

hope your help sir for Loop Approach.

Regards,

Chirag Raval