Update Macro

  • Thread starter Thread starter Yeshwin
  • Start date Start date
Y

Yeshwin

Hi All,

I have a worksheet with data in it. Based on the data also have a chart on
the same sheet plotted by rows. Using the macro below I am deleting rows
containing value color.

Dim r As Range
Dim lastRow As Long
Dim i As Long
Set r = ActiveSheet.UsedRange
lastRow = r.Rows(r.Rows.Count).Row
For i = lastRow To 3 Step -1
If InStr(1, Cells(i, 2), "Color", vbTextCompare) = 1 Then
Rows(i).Delete
End If
Next
ActiveSheet.UsedRange.Select
End Sub


Since I have the chart plotted by rows (Actually it should not matter wether
the chart is plotted by rows or by columns) I want to write a macro or
modify the macro I have above in a way that it assigns new range as a
source data for the chart.

Please help me

Yesh.
 
If you are deleting rows within the currently charted range, the chart
should adjust automatically.
 
Thanks for the reply, Tom. That's what I thought. But it adjusts only if the
chart is plotted by Columns but I am having trouble with charts plotted by
rows. There I get #ref for the rows missing

This is the reason I was wondering if you or anyone here could help me reset
or update the chart using macro so that we can assign the updated range.

Thanks

Yesh
 
If you destroy the entire source for the data, that would be correct.

I think you would just loop through the series and delete the series where
the formula has #Ref in it.

something like (after you delete the rows)

Dim ser as Series
for each ser in activechart.SeriesCollection
if instr(1,ser.Formula,"#REF",vbTextCompare) then
ser.Delete
end if
Next


Untested pseudo code, but I would think that would work.
 
Thanks a lot, Tom. Worked perfectly.

Yesh
Yeshwin said:
Thanks for the reply, Tom. That's what I thought. But it adjusts only if the
chart is plotted by Columns but I am having trouble with charts plotted by
rows. There I get #ref for the rows missing

This is the reason I was wondering if you or anyone here could help me reset
or update the chart using macro so that we can assign the updated range.

Thanks

Yesh

chart
Click to expand...
 
Back
Top