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 should be no need to select anything to do this you could just loop through the columns.

For I = Range("A1").Column To Range("G1").Column Step 2
     Columns(I).ColumnWidth = 20
Next I

When I run that code columns A, C, E and G have their column width set to 20.

 

[largeselection]

Sorry for being vague in my post. I know I could put in code as you sent over to apply to that range. I'm shooting for something that is more variable so it can be used in a wider set of situations (as I have changing needs for this). In addition, the column width was only an example, sometimes I have to change other formatting things. Basically, I'm looking for a way to simplify the tedious process of having to hold CTRL+ and selecting all the columns I want to apply the changes to as I sometimes need to highlight 25 columns to make adjustments too across 50 worksheets.

Thanks for weighing in! I really appreciate it.

 

[Norie]

I kind of thought you might want something a bit more flexible and I know what I posted was basically hardcoded.

Did it help at all? I was just sort of trying to demonstrate the basic concept.

Also, no matter what formatting you want to do it's rarely, if ever, needed to select either ranges or worksheets.

You just need to make sure you reference things properly.

If you are working with multiple worksheets then you can just loop through them.

Something like this.

For Each ws In Worksheets
        For I = Range("A1").Column To Range("G1").Column Step 2
            ws.Columns(I).ColumnWidth = 20
        Next I
Next ws

Again not a great example but hopefully you'll get the idea.

 

[6StringJazzer]

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

 

[supdawg]

Just change whatever you are looking for into a variable.

To piggyback off of Norie's post.

c = inputbox("Enter # columns to select", "Using the VBA Input Box")


For I = Range("A1").Column To Range("H1").Column Step c
     Columns(I).ColumnWidth = 20
Next I

Where c = columns you want to select.

 

[VoG]

See #4 http://www.mrexcel.com/forum/showthread.php?t=451452

 

[6StringJazzer]

Here is modified code to ask the user for the interval. This actually selects the columns (as does my code above).

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
   
   Dim stepSize As Long
   Dim stepStr As String
   
   On Error GoTo NumberError
   stepStr = InputBox("Enter column interval")
   stepSize = stepStr
   On Error GoTo 0
   
    
   For i = firstCol To lastCol Step stepSize
      columnLetter = Chr(i + 65 - 1)
      rangeString = rangeString & "," & columnLetter & ":" & columnLetter
   Next i
    
   rangeString = Mid(rangeString, 2)
   Debug.Print rangeString
   Range(rangeString).Select
   
   Exit Sub
   
NumberError:
   
   MsgBox "You entered:  " & stepStr & vbCrLf & "You must enter a valid number"
    
    
End Sub

 

[largeselection]

Thanks for all your responses!

I tinkered with the code you sent 6string and this code is accomplishing what I want it to so far:

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

Thanks again for all of your inputs.

 

[GTO]

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

I want to select the data block from A1-G100

Presuming you just want the columns selected for the rows in the original selection, maybe:


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> WackySelect()<br><SPAN style="color:#00007F">Dim</SPAN> lRet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rCorner <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rCol <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lRet = Application.InputBox("Every x Col?", "", , , , , , 1)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> lRet >= 2 <SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#00007F">Set</SPAN> rCorner = Selection(1).Resize(Selection.Rows.Count)<br>        <SPAN style="color:#00007F">Set</SPAN> rCol = rCorner<br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Selection.Columns.Count <SPAN style="color:#00007F">Step</SPAN> lRet<br>            <SPAN style="color:#00007F">Set</SPAN> rCol = Application.Union(rCol, rCorner.Offset(, i - 1))<br>        <SPAN style="color:#00007F">Next</SPAN><br>        <br>        rCol.Select<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark