Skip rows when Charting data with invalid/missing data

[BS-SPS]

I am trying to chart project milestone dates off of a status spreadsheet. A simplified example follows:
PROJECT1 10/01/2004
PROJECT2 1/1/2004
PROJECT3 "N/A"
PROJECT4 10/01/2004
PROJECT5
PROJECT6 10/15/2004
PROJECT7 12/31/2004

If the formatting is kept, you'll see 2 columns, project name and date.
If all names and dates are present (rows 1 & 2) the bar chart works fine. But add an invalid date (row 3) or missing date (row 5) and the chart doesn't work.

I must allow the invalid dates, but need to report around them. I can create a new spreadsheet and map over the cells then replace the invalid values with 1/1/04, but projects with invalid dates should not appear on the chart.
I'd like to skip the invalid rows when I map, but haven't a clue how to do so without leaving a blank row!
I've spent a LOT of time searhing here and on the internet, and can't find anything to help... though I suspect there is an easy answer somewhere.
Thank you for your time.

 

[Andrew Poulsom]

AutoFilter your data for column B (date) is greater than zero. Then make sure Plot visible cells only is checked under Tools|Options|chart tab.

 

[BS-SPS]

THANK YOU!! I suspected there was an easy way to accomplish... however this leads to another problem I haven't been able to figure yet.

Do you know how to apply a filter when the workbook or spreadsheet is opened? Each time the main data sheet is updated I would need to refresh the filtering.

Should I open a new thread?

 

[Andrew Poulsom]

You could put some code in the Workbook_Open or Worksheet_Change event procedure to refresh the AutoFilter. Record a macro while doing it manually and you will get the relevant code.