Selecting a range from a spreadsheet

[rmullen]

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

 

[Guest]

hi,

Try this which assumes data starts from column 1;

Sub CreateRanges()


Dim Lastrow As Long
Dim Lastcol As Integer
Dim Col As Integer
Dim ws1 As Worksheet

Dim AgeSeries As Range
Dim NameSeries As Range
' Add other range as required .....

Set ws1 = Worksheets("Sheet2")

With ws1
Lastcol = .Cells(6, Columns.Count).End(xlToLeft).Column
Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

For Col = 1 To Lastcol

Select Case .Cells(6, Col)
'if the name is age, then set the range to the stuff below
Case "Age"
Set AgeSeries = .Range(.Cells(7, Col), .Cells(Lastrow,
Col))
Case "Name"
Set NameSeries = .Range(.Cells(7, Col), .Cells(Lastrow,
Col))
Case ("Date")
' ...
End Select

Next Col
End With
For Each cell In AgeSeries
Debug.Print cell
Next
End Sub