Selecting a given 'number' of columns...

[pdbowling]

Hi, everybody,
I have a query that is displayed in Excel. I receive a variable that tells me how many columns were returned from the table. I need to select the region of the data for a graph (in code). Since Excel spreadsheets header the columns with letters, the only method I can think of for this (very cumbersome) is a giant Select Case statement. I typically get between 30 to 50 columns back which would be at least a twenty member Case. Just seems like theres a better way...

Select Case ColumnCount

Case 30
last_Column = "AD"
Case 31
last_Column = "AE"
...
Case 50
last_Column = "AX"

end case

selectRegion = "A1:" & last_Column & "2"

Range(selectRegion).select
'///launch graph macro


Any better suggestions?
Thanks all
PB

 

[Fornatian]

Try:

ActiveSheet.UsedRange

 

[Tim K.]

Or use this function to replace the Select Case.

Function GetRange(intX As Integer) As String
Dim intY As Integer
If intX < 27 Then
    GetRange = Chr(64 + intX)
Else
    intY = intX Mod 26
    intX = intX \ 26
    GetRange = Chr(64 + intX) & Chr(64 + intY)
End If
End Function

selectRegion = "A1:" & GetRange(ColumnCount) & "2"