Select Alternate Columns

I often like to have narrow empty columns between data columns just to make things look nice (cell underlining looks better that way in my opinion). This macro will prompt you for a number of columns per group and then it selects one column per group for the currently selected range (i.e. selecting A5:G5, running the macro and entering 2 would result in columns B, D, and F being selected). Then you can quickly resize those columns to make everything look real pretty.

' Select every other column
' Copyright under GPL by Mark Grimes
'
Sub mgSelectEOther()
    Dim i, mult As Integer
    Dim r, cst As String

    mult = Application.InputBox(prompt:="Select every x columns:", default:=2, Type:=1)

    r = ""
    i = 0
    For Each c In Selection
        i = i + 1
        If i Mod mult = 0 Then
            If (c.Column > 26) Then
                ' tx = c.Column & ": A=" & Asc("A") & ", " & Int(c.Column / 26) & ", " & (c.Column Mod 26)
                ' MsgBox tx
                cst = Chr(Asc("A") - 1 + Int(c.Column / 26)) & Chr(Asc("A") + (c.Column Mod 26) - 1)
            Else
                cst = Chr(Asc("A") + c.Column - 1)
            End If
            r = r & "," & cst & ":" & cst
        End If
    Next
    r = Right(r, Len(r) - 1)
    ' MsgBox r
    ActiveSheet.Range(r).Select
End Sub

Published

July 25, 2005 10:36AM

Tags

License

The contents of this blog are licensed under the Creative Commons “Attribution-Noncommercial-Share Alike 3.0″ license.