Results 1 to 4 of 4
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Selecting columns with VBA (2000 +)

    My Macro gets a last data column lngLastCol. Somewhere in the dim recesses of my mind I thought there was a way to select columns with column numbers, as can be done with rows:

    Rows("2:5").Select

    but even in R1C1 Reference style:

    Columns("2:5").Select

    is not valid where:

    Columns("B:E").Select

    is. I'm trying to get a short way to .Columns("3:" & lngLastCol), but for now I'm stuck at:

    With Range(rngSource(1, 3), rngSource(1, lngLastCol)).EntireColumn

    Isn't there a simpler way? Am I overlooking a syntax option? (I feel a D'oh! moment coming on.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting columns with VBA (2000 +)

    Excel VBA accepts only A1 style references when working with ranges (strangely enough, it requires R1C1 style references in some other situations). So you'll have to use a methid such as you're using now, or you could use a function that converts column numbers to letters, e.g.

    Function ColLetter(ColNumber As Integer) As String
    If ColNumber < 1 Or ColNumber > 256 Then
    Exit Function
    End If
    If ColNumber <= 26 Then
    ColLetter = Chr(64 + ColNumber)
    ElseIf ColNumber Mod 26 = 0 Then
    ColLetter = Chr(64 + (ColNumber - 1) 26) & "Z"
    Else
    ColLetter = Chr(64 + (ColNumber - 1) 26) & Chr(64 + ColNumber Mod 26)
    End If
    End Function

    There are other versions around that may be more efficient.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Selecting columns with VBA (2000 +)

    In addition to Hans' points, the closest I think you can get to the syntax you wanted would be:
    <code>Range(Columns(2), Columns(5)).Select</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selecting columns with VBA (2000 +)

    Aha, that's more concise, thanks.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •