Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoselect data using Macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoselect data using Macros
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoselect data using Macros
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 wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autoselect data using Macros
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 wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macros for Data Entry? | New Users to Excel | |||
AutoSelect? in Drop Down List Box | Excel Worksheet Functions | |||
Macros for copying the data | Excel Programming | |||
pasting data and macros | Excel Discussion (Misc queries) | |||
VBA Autoselect problem | Excel Programming |