Autoselect data using Macros

[[email protected]]

Hi,
Here is what I am trying to achieve in Excel.

Have sheet where the user will enter data. The range of data can be
variable each time. between 4-8 rows and two columns (A and B).

I want to write a macro that will do the following:

1. Find the first blank row in column A and now it has the range of
valid data.
2. Use the range from Step 1 to create an XY chart where data in Col A
is X Axis and Col B is y-axis.
3. Label the chart and place it on the same sheet as data.

Please let me know how to handle dynamic/variable range selection in
Excel Macros.

Thanks,
Shoaib

[Tom Ogilvy]

set rng = Range(Cells(1,1),Cells(1,1).End(xldown))

or if there will be blank rows in the data

set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))

now you can use rng to build your chart.

not
set rng = rng.resize(,2) will expand the range to include two columns
set rng2 = rng.offset(0,1) will set a separate reference to column B.

Turn on the macro recorder and build the chart manually.

then modify the code recorded to use the code above.

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> Hi,
> Here is what I am trying to achieve in Excel.
>
> Have sheet where the user will enter data. The range of data can be
> variable each time. between 4-8 rows and two columns (A and B).
>
> I want to write a macro that will do the following:
>
> 1. Find the first blank row in column A and now it has the range of
> valid data.
> 2. Use the range from Step 1 to create an XY chart where data in Col A
> is X Axis and Col B is y-axis.
> 3. Label the chart and place it on the same sheet as data.
>
> Please let me know how to handle dynamic/variable range selection in
> Excel Macros.
>
> Thanks,
> Shoaib
>

[Mike Fogleman]

Shoaib: Here is how to find the last row of data in a column and set that as
a range:
Dim Lrow As Long
Dim rng as Range

Lrow = Cells(Rows.Count, "A").End(xlUp).Row '"A" can be any column
Set Rng = Range("A2:A" & Lrow) 'starts at row 2 to allow for header

From here you can merge a recorded macro of creating your chart.

Mike F

<[email protected]> wrote in message
news:[email protected]...
> Hi,
> Here is what I am trying to achieve in Excel.
>
> Have sheet where the user will enter data. The range of data can be
> variable each time. between 4-8 rows and two columns (A and B).
>
> I want to write a macro that will do the following:
>
> 1. Find the first blank row in column A and now it has the range of
> valid data.
> 2. Use the range from Step 1 to create an XY chart where data in Col A
> is X Axis and Col B is y-axis.
> 3. Label the chart and place it on the same sheet as data.
>
> Please let me know how to handle dynamic/variable range selection in
> Excel Macros.
>
> Thanks,
> Shoaib
>

[[email protected]]

Tom,
Thanks for the response. I thought I would be able to handle this on my
own but I guess I am a real novice at VBA.

One thing that I forgot to mention was that after finding the range of
data I would also like to sort the data based on Column A and then hand
it over to the chart. Any suggestions there?

Thanks,
Shoaib


Tom Ogilvy wrote:
> set rng = Range(Cells(1,1),Cells(1,1).End(xldown))
>
> or if there will be blank rows in the data
>
> set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
>
> now you can use rng to build your chart.
>
> not
> set rng = rng.resize(,2) will expand the range to include two columns
> set rng2 = rng.offset(0,1) will set a separate reference to column B.
>
> Turn on the macro recorder and build the chart manually.
>
> then modify the code recorded to use the code above.
>
> --
> Regards,
> Tom Ogilvy
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > Here is what I am trying to achieve in Excel.
> >
> > Have sheet where the user will enter data. The range of data can be
> > variable each time. between 4-8 rows and two columns (A and B).
> >
> > I want to write a macro that will do the following:
> >
> > 1. Find the first blank row in column A and now it has the range of
> > valid data.
> > 2. Use the range from Step 1 to create an XY chart where data in Col A
> > is X Axis and Col B is y-axis.
> > 3. Label the chart and place it on the same sheet as data.
> >
> > Please let me know how to handle dynamic/variable range selection in
> > Excel Macros.
> >
> > Thanks,
> > Shoaib
> >

[[email protected]]

Ok so I figured out the sorting part. Here is what I have now

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
Set rng = rng.Resize(, 2)
Set rng2 = rng.Offset(0, 1)
rng.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Sterngth Duration
Curve").Range _
"A2:B9"), PlotBy:=xlColumns


Where the last line for selecting data source was created by the Macro
recording tool. How do I modify that line to take the range from rng
variable insted of a fixed range every time the macro is run.

Also is there a way to look for a chart on a sheet, delete that chart
and then create a new one using the above code.

Thanks
Shoaib

[email protected] wrote:
> Tom,
> Thanks for the response. I thought I would be able to handle this on my
> own but I guess I am a real novice at VBA.
>
> One thing that I forgot to mention was that after finding the range of
> data I would also like to sort the data based on Column A and then hand
> it over to the chart. Any suggestions there?
>
> Thanks,
> Shoaib
>
>
> Tom Ogilvy wrote:
> > set rng = Range(Cells(1,1),Cells(1,1).End(xldown))
> >
> > or if there will be blank rows in the data
> >
> > set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
> >
> > now you can use rng to build your chart.
> >
> > not
> > set rng = rng.resize(,2) will expand the range to include two columns
> > set rng2 = rng.offset(0,1) will set a separate reference to column B.
> >
> > Turn on the macro recorder and build the chart manually.
> >
> > then modify the code recorded to use the code above.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > > Here is what I am trying to achieve in Excel.
> > >
> > > Have sheet where the user will enter data. The range of data can be
> > > variable each time. between 4-8 rows and two columns (A and B).
> > >
> > > I want to write a macro that will do the following:
> > >
> > > 1. Find the first blank row in column A and now it has the range of
> > > valid data.
> > > 2. Use the range from Step 1 to create an XY chart where data in Col A
> > > is X Axis and Col B is y-axis.
> > > 3. Label the chart and place it on the same sheet as data.
> > >
> > > Please let me know how to handle dynamic/variable range selection in
> > > Excel Macros.
> > >
> > > Thanks,
> > > Shoaib
> > >