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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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,171,686
Messages
5,876,893
Members
433,217
Latest member
Muhammad Tanzeel Ur Rehma

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