Filtering Out Blank Entries

DaveOG

New Member
Joined
Sep 26, 2006
Messages
8
I am using a simple for loop to go through a set of data and add a series of data to a chart. However, if a blank row is left in the data the chart is not produceed correctly.

Here is the original code that works when all data is filled in.
'Addition of Data Series
For i = 1 To NoOfLines
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).XValues = Worksheets(i + 1 + NoOfFields).[I3:I500]
ActiveChart.SeriesCollection(i).Values = Worksheets(i + 1 + NoOfFields).[L3:L500]
ActiveChart.SeriesCollection(i).Name = Worksheets(i + 1 + NoOfFields).Cells(1, 1)
Next
I've tried putting in If statements to remove the series with no data but it doesn't work.

Any help would be awesome!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
options exist in excel itself to remove blank rows.

try this:
Select all rows of your data

goto edit > chose go to > choose special > select blanks > press OK
All blanks will be highlighted.
Now goto edit and choose delete > choose cells up or left based on what you want.

Let me know how it worked for you

Ravi
 

Forum statistics

Threads
1,137,204
Messages
5,680,166
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top