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.

--
Regards,
Tom Ogilvy


"Yeshwin" wrote in message
...
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.

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" wrote in message
...
If you are deleting rows within the currently charted range, the chart
should adjust automatically.

--
Regards,
Tom Ogilvy


"Yeshwin" wrote in message
...
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 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.

--
Regards,
Tom Ogilvy



"Yeshwin" wrote in message
...
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" wrote in message
...
If you are deleting rows within the currently charted range, the chart
should adjust automatically.

--
Regards,
Tom Ogilvy


"Yeshwin" wrote in message
...
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.

Thanks a lot, Tom. Worked perfectly.

Yesh
"Yeshwin" wrote in message
...
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" wrote in message
...
If you are deleting rows within the currently charted range, the chart
should adjust automatically.

--
Regards,
Tom Ogilvy


"Yeshwin" wrote in message
...
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.