Selecting a range from a dynamic column heading

Hi All -

I'm trying to select a range from a spreadsheet from a button click
(and then user will see a graph popup - he will NOT have to select
anything ..just click on the button).

Problem is that there is that the spreadsheet is dynamic with each user
(depending on what columns he wants to view).

for Person 1 spreadsheet could look like:

name date state age height
weight
jack 5/2/05 TX 54 126
205
jack 5/3/05 TX 54 126
206
jack 5/4/05 TX 54 126
201

for person 2 spreasheet could look like:

name age height weight date
state
jack 56 TX 205 5/2/05
TX
jack 56 TX 206 5/3/05
TX
jack 56 TX 201 5/4/05
TX

The column headings are always in the same row (Row 6).

If you could help me out with selecting the range, for each category
(age, weight, height, date), that would be awesome!

Here's what i have so far, that definitely doesn't work...
Dim RowStart As Integer
Dim ColCount As Integer
Dim ColStart As Integer
Dim rngA As range

ColCount = Columns.Count
ColStart = ThisWorkbook.ActiveSheet.Cells(1, 1).Column
RowStart = ThisWorkbook.ActiveSheet.Cells(1, 1).Row

For Each rngA In ActiveSheet
Offset
For i = ColStart + 1 To ColStart + ColCount - 1
'this is supposed to look at the column heading and read the
name
Select Case ThisWorksheet.CurrentRegion.Cells(6, i)
'if the name is age, then set the range to the stuff below
Case "age"
Set ageSeries =
ThisWorksheet.range(ThisWorksheet.CurrentRegion.Cells(7, i))
End Select
Next i
Next rngA

Thanks again so much for any help you have!!!
rebekah

How about just continuing your select case stuff and get all the ranges:

Option Explicit
Sub testme()
Dim NameRng As Range
Dim DateRng As Range
Dim StateRng As Range
Dim AgeRng As Range
Dim HeightRng As Range
Dim WeightRng As Range

Dim FirstRow As Long
Dim LastRow As Long
Dim LastCol As Long
Dim iCol As Long

Set NameRng = Nothing
Set DateRng = Nothing
Set StateRng = Nothing
Set AgeRng = Nothing
Set HeightRng = Nothing
Set WeightRng = Nothing

With ActiveSheet
FirstRow = 1 'where headers are
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = 1 To LastCol
Select Case LCase(.Cells(1, iCol).Value)
Case Is = "name"
Set NameRng = .Range(.Cells(1, iCol), .Cells(LastRow, iCol))
Case Is = "date"
Set DateRng = .Range(.Cells(1, iCol), .Cells(LastRow, iCol))
Case Is = "state"
Set StateRng = .Range(.Cells(1, iCol), .Cells(LastRow, iCol))
Case Is = "age"
Set AgeRng = .Range(.Cells(1, iCol), .Cells(LastRow, iCol))
Case Is = "height"
Set HeightRng = .Range(.Cells(1, iCol), .Cells(LastRow, iCol))
Case Is = "weight"
Set WeightRng = .Range(.Cells(1, iCol), .Cells(LastRow, iCol))
End Select
Next iCol

If NameRng Is Nothing _
Or DateRng Is Nothing _
Or StateRng Is Nothing _
Or AgeRng Is Nothing _
Or HeightRng Is Nothing _
Or WeightRng Is Nothing Then
MsgBox "Not all ranges found!"
Exit Sub
End If

'your code to create the chart.

End With

End Sub


roadie.girl@gmail.com wrote:
>
> Hi All -
>
> I'm trying to select a range from a spreadsheet from a button click
> (and then user will see a graph popup - he will NOT have to select
> anything ..just click on the button).
>
> Problem is that there is that the spreadsheet is dynamic with each user
> (depending on what columns he wants to view).
>
> for Person 1 spreadsheet could look like:
>
> name date state age height
> weight
> jack 5/2/05 TX 54 126
> 205
> jack 5/3/05 TX 54 126
> 206
> jack 5/4/05 TX 54 126
> 201
>
> for person 2 spreasheet could look like:
>
> name age height weight date
> state
> jack 56 TX 205 5/2/05
> TX
> jack 56 TX 206 5/3/05
> TX
> jack 56 TX 201 5/4/05
> TX
>
> The column headings are always in the same row (Row 6).
>
> If you could help me out with selecting the range, for each category
> (age, weight, height, date), that would be awesome!
>
> Here's what i have so far, that definitely doesn't work...
> Dim RowStart As Integer
> Dim ColCount As Integer
> Dim ColStart As Integer
> Dim rngA As range
>
> ColCount = Columns.Count
> ColStart = ThisWorkbook.ActiveSheet.Cells(1, 1).Column
> RowStart = ThisWorkbook.ActiveSheet.Cells(1, 1).Row
>
> For Each rngA In ActiveSheet
> Offset
> For i = ColStart + 1 To ColStart + ColCount - 1
> 'this is supposed to look at the column heading and read the
> name
> Select Case ThisWorksheet.CurrentRegion.Cells(6, i)
> 'if the name is age, then set the range to the stuff below
> Case "age"
> Set ageSeries =
> ThisWorksheet.range(ThisWorksheet.CurrentRegion.Cells(7, i))
> End Select
> Next i
> Next rngA
>
> Thanks again so much for any help you have!!!
> rebekah

--

Dave Peterson

ah ha! thank you so much dave - i wasn't doing the Set rangeName =
Range.....etc. this works great! thanks for your help again