Can I create a chart using every fifth row of data?
 

I have a spreadsheet with 13,000 rows of data. I need to creat a chart using
every 5th row in order to cut down the amount of data in my chart. Can I
easily do this?

Can I create a chart using every fifth row of data?
 

I'm not sure of an easy way to do this, but if you hold the CTRL key and
select evert fifth row and then the chart wizzard, it will only use those
rows. However, with 13,000 rows of data that means clicking 2,600 times.

"Adair72" wrote:

I have a spreadsheet with 13,000 rows of data. I need to creat a chart using
every 5th row in order to cut down the amount of data in my chart. Can I
easily do this?

Can I create a chart using every fifth row of data?
 

I recently had to solve this same problem with a change to some data used for
the charts. In my case, i had to chart This Year vs Last Year data, pulling
sub-totals out of a large block of cells

My VBA solution involved:
creating a string of addresses of the required cells,
copying the range,
pasting them into a contiguous range on a hidden sheet,
adding this range as a Named Range,
and assigning this named range to the Values property for the
seriescollection on the chart

You should be able to just step through and create your series string,
stepping by 5

As i cannot find my code, here is some pseudo-code from my notes:


Dim wkRange as worksheet
Dim wkOrig as worksheet

Set wkRange = worksheets("InvisibleRangeSheet")
Set wkOrig = worksheets("originalSheet")

With Range

For i = 1 to cells(rows.count, "A").End(xlup).row Step 5
strSeries = strSeries & ", " & .cells(i,1).address
Next i

wkOrig.range(strSeries).copy
.range(.cells(row,col).address).pastespecial
paste:=xlpastevaluesandnumberformats
.names.add name:="RangeName" refersTo:=.range(.cells(row,col).address &
":" & .cells(row+wkOrig.range(strSeries).count -1,col).address)

ActiveChart.seriescollection(1).values = "='" & .name & "'!" & "RangeName"

End With



HTH

sqlfan13


"joebogey" wrote:

I'm not sure of an easy way to do this, but if you hold the CTRL key and
select evert fifth row and then the chart wizzard, it will only use those
rows. However, with 13,000 rows of data that means clicking 2,600 times.

"Adair72" wrote:

I have a spreadsheet with 13,000 rows of data. I need to creat a chart using
every 5th row in order to cut down the amount of data in my chart. Can I
easily do this?

Can I create a chart using every fifth row of data?
 

Of course, you'll hit a limit based on the maximum number of characters you
can pass to the chart for its .Values, .XValues, or .Formula. The Excel
Chart FAQ has a formulaic technique to display every Nth point:

http://pubs.logicalexpressions.com/P...?ID=209#jon024

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"sqlfan13" <nospam.dustinmueller_13.at.hotmail.com wrote in message
...
I recently had to solve this same problem with a change to some data used
for
the charts. In my case, i had to chart This Year vs Last Year data,
pulling
sub-totals out of a large block of cells

My VBA solution involved:
creating a string of addresses of the required cells,
copying the range,
pasting them into a contiguous range on a hidden sheet,
adding this range as a Named Range,
and assigning this named range to the Values property for the
seriescollection on the chart

You should be able to just step through and create your series string,
stepping by 5

As i cannot find my code, here is some pseudo-code from my notes:


Dim wkRange as worksheet
Dim wkOrig as worksheet

Set wkRange = worksheets("InvisibleRangeSheet")
Set wkOrig = worksheets("originalSheet")

With Range

For i = 1 to cells(rows.count, "A").End(xlup).row Step 5
strSeries = strSeries & ", " & .cells(i,1).address
Next i

wkOrig.range(strSeries).copy
.range(.cells(row,col).address).pastespecial
paste:=xlpastevaluesandnumberformats
.names.add name:="RangeName" refersTo:=.range(.cells(row,col).address &
":" & .cells(row+wkOrig.range(strSeries).count -1,col).address)

ActiveChart.seriescollection(1).values = "='" & .name & "'!" &
"RangeName"

End With



HTH

sqlfan13


"joebogey" wrote:

I'm not sure of an easy way to do this, but if you hold the CTRL key and
select evert fifth row and then the chart wizzard, it will only use those
rows. However, with 13,000 rows of data that means clicking 2,600 times.

"Adair72" wrote:

I have a spreadsheet with 13,000 rows of data. I need to creat a chart
using
every 5th row in order to cut down the amount of data in my chart. Can
I
easily do this?