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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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
 
Back
Top