Select every other column in vba

[largeselection]

Hi,

I bet this is an easy question and as soon as someone answers I'll be kicking myself for not thinking about it...

but how can I just select every other (nth) column based on a selection?

So say I have data in columns A-G. I want to select the data block from A1-G100 and then run this macro so that it keeps just every other (or nth) column selected. So the process would be to highlight the data block, then run the macro and I would be left with the data block every other (nth) column selected so I could format all those cells the same way (column width adjustment so I can't use conditional formatting with =mod(),2).

To take it to another slightly more complicated step- I'd like to ultimately include a box that pops up and asks for how many columns I'd like in between.

So that I could select a data block, then hit a button for the macro, type in a 3 to indicate I want to select every 3rd column and then hit go, and then be left with excel having every 3rd column selected.

Any ideas?

Thanks.

 

[Norie]

There really isn't any need to Select, and in fact it can cause problems - especially if you are working with multiple worksheets.

But if that's what you want and what's been posted that's the important thing.

 

[largeselection]

GTO, I ended up using your code. Mine (courtesy of 6string with advice from norie) was working for awhile, but I started noticing it bombed out if I selected too many columns so I'm going to tinker with that and see if I can figure out what the issue is just for my own education.

Thanks!

 

[Norie]

So don't select any columns.

 

[6StringJazzer]

There really isn't any need to Select, and in fact it can cause problems - especially if you are working with multiple worksheets.

But if that's what you want and what's been posted that's the important thing.

This is an important point. I have often explained why it's unnecessary to Select ranges in VBA, but in this case I decided to give him what he asked for instead of what he needs because it sounded like there was more than just one kind of formatting he might want to do, and the code would have to be modified every time the formatting need changed.

Plus it was fun to write and I also learned about Application.Union from reading other answers

 

[largeselection]

You are correct 6 string. Generally I have learned to eliminate the select when not necessary from code I use, but in this case I wanted to literally leave the columns selected so that I could apply all sorts of formatting to the selection. Thanks for all the help guys.

 

[infexcel]

This only works for columns from a to z, when I select AA and so on, there is an ERROR.

Runtime Error 1004

Method 'Range' of object '_Global' failed


Any ideas?. Thank you

This code will select every other column (entire columns) in the range of columns within the current selection. Figuring out how to do this with other frequencies is an exercise for the student

Sub EveryOtherColumn()
 
   Dim rangeString As String
   Dim columnLetter As String
   Dim i As Long
 
   Dim firstCol, lastCol As Long
   firstCol = Selection.Column
   lastCol = Selection.Columns.Count + firstCol - 1
 
   For i = firstCol To lastCol Step 2
     columnLetter = Chr(i + 64)
     rangeString = rangeString & "," & columnLetter & ":" & columnLetter
   Next i
 
   rangeString = Mid(rangeString, 2)
 
   Range(rangeString).Select
 
End Sub

 

[6StringJazzer]

Just saw this post, sorry for the delay, hope this is still helpful

Sub EveryOtherColumn()
 
   Dim rangeString As String
   Dim columnLetter As String
   Dim i As Long
 
   Dim firstCol, lastCol As Long
   firstCol = Selection.Column
   lastCol = Selection.Columns.count + firstCol - 1
 
   
   For i = firstCol To lastCol Step 2
      rangeString = rangeString & "," & Cells(1, i).EntireColumn.Address
   Next i
 
   rangeString = Mid(rangeString, 2)
   Range(rangeString).Select
 
End Sub

 

[Legacy 287428]

This is a very helpful thread, and I was trying to follow the steps, and experiment some, but I am frankly coming up short somewhere. I was in the process of selecting every other column from H through XFD, and when I got to WTF (legitimately to this column, ironic, no?, but also started from XFD and went back) I saw this post, thank you! Basically, I am hoping to use two instances of this to size every other cell starting at cell H7 to 110px, then to simultaniously size everyother cell starting at cell I7 to 25px. (Im newer to Excel's VBA, working on a spreadsheet, that is my most complicated to date, and just trying to not get to frustrated). The other component I am trying to do is link a checkbox to the column through a number of cells, but I think that is another problem for another day.

Sub EveryOtherColumn()
    Dim rangeString As String
    Dim columnLetter As String
    Dim i As Long
 
    Dim firstCol, lastCol As Long
    firstCol = Selection.Column
    lastCol = Selection.Columns.Count + firstCol - 1

    For i = Range("H7").Column To Range("XFD").Column Step 2
         Columns(i).ColumnWidth = 110
    Next i
    
    For i = Range("i7").Column To Range("XFD").Column Step 2
         Columns(i).ColumnWidth = 25
    Next i
    
    rangeString = Mid(rangeString, 2)
    Range(rangeString).Select
    
End Sub

Thank you for any help or suggestions!

-Sean