Creating graph with variable range of data

random_noise

random_noise

Active Member
Joined
Dec 19, 2007
Messages
367
I've got a macro which automatically inputs data from a text file & splits it up into chunks and basically creates a seperate sheet for each chunk (which represents a days worth) of data.

What Im now trying to do is have a chart automatically created with the data and created as a new sheet.

The number of rows in the chart can vary so I'm having difficulty automatically selecting the correct range for the chart source data.

I'm really not sure how the whole range function works but I'm sure there must be a way to automatically select all the data up to where there is no more quickly & easily.

At the moment the (non-working) code I have is:

Code: 
             Range("B1").Select
                    Set chartrange = Sheets(varsheetname).Range(ActiveCell, ActiveCell.End(xlDown))
                    
                    ' test add in chart
                    Charts.Add
                        ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
                        ActiveChart.SetSourceData Source:=Sheets(varsheetname).Range(chartrange), _
                            PlotBy:=xlColumns
I think I'm going about this completely wrong.

I basically need to select from B1:C{whatever} as my source data. Normally there are 1000s of rows so I'd rather not have to use some kind of counter to work out where the last row is.

Any ideas where I'm going wrong (apart from everywhere!)?

Cheers

Dave
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
I received this as a way to find a range of cells from another member of the board. It has helped me tremendously.

Code: 
Sub FindLastRow()

'This finds the last row that is occupied in column A
'It acts by going to cell A65535, then doing the VBA equivalent
'of [Ctrl] + [up arrow]

Dim lngLastRow as Long
lngLastRow = Range("A65535").End(xlUp).Row

Set r = Range("A1:K" & lngLastRow)

Basically, that will find the last row of data you have on a spreadsheet and then you can use that within your range statement. So the above will set "A1" through "K" & (whatever the last row of data is) as the range.

HTH
Roger
 
Upvote 0
Thanks very much Roger!

It didn't quite work using the set r = .....

but by putting the 2nd last line directly into my chart source statement it worked!

I'm still confused about how the whole range thing works in so far as I can't seem to have a variable which contains a range (there doesnt seem to be a range type??) but at least my macro does what it's supposed to.

Cheers!

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Creating Auto Updating Charts
Replies
2
Views
622
Greencells
G
D
  • Question
Determine range for chart data using userform input - VBA
Replies
0
Views
359
dlee83
D
A
  • Question
VBA problem with setting data source range
Replies
1
Views
854
Akbarov
A
B
  • Question
Using 3D reference to create graph
Replies
0
Views
275
BigKeyes13K
B
A
  • Question
Import Table Data from Web using VBA to excel Worksheet and able to input Values which can modify the web data to be extract
Replies
0
Views
357
Ambrosia
A

Forum statistics

Threads
1,214,950
Messages
6,122,436
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top