Selecting columns with VBA (2000 +)

[JohnBF]

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.)

[HansV]

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.

[rory]

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>

[JohnBF]

Aha, that's more concise, thanks.