Update Macro

[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.

 

[Tom Ogilvy]

If you are deleting rows within the currently charted range, the chart
should adjust automatically.

 

[Yeshwin]

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

 

[Tom Ogilvy]

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.

 

[Yeshwin]

Thanks a lot, Tom. Worked perfectly.

Yesh

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