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?
|