Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range from a spreadsheet
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.Ce lls(7, i)) End Select Next i Next rngA Thanks again so much for any help you have!!! rebekah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a range from a spreadsheet
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 "rmullen" 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.Ce lls(7, i)) End Select Next i Next rngA Thanks again so much for any help you have!!! rebekah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Data on a Spreadsheet | Excel Discussion (Misc queries) | |||
selecting spreadsheet using tabs? | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
The cursor on my Excel spreadsheet is not selecting cells. | Excel Discussion (Misc queries) | |||
Selecting rows in a spreadsheet | Excel Programming |