Extraneous Dates Showing in Dynamic Scatter Chart

Dynamic range based on entry in D21 that limits the number of days (Rows) to display.
Chart populates extra dates on front and back and weekend/holiday dates which are not part of the values presented in A3:A17.
I've tried UDF, Named Ranges, Direct and indirect Cell References with no success. Also tried to change the Type to Text but not available in Excel 2016 that I can find.
Axis Boundaries Min/Max only take values or auto and no formula, cell reference or named reference are accepted.
In the attached I'm commented out the UDF lines and the two cell references in F18 and F19. None of them worked in the desired manner and are included to demonstrate how far I've taken this.
Sheet is presently loaded with just one set of values from column B, and the reference date range in A but still populates extraneous/unlisted date values.
Goal is to reduce the clutter and just present the contents (dates and percentages) from the cells in the sheet each time D21 is changed.

HI
You must indicate the start date (as Long) and the end date in two separate cells.
Then use this code (obtained from the macro recorder) to call from the Change event of cell D21

Please Login or Register  to view this content.

I'm not sure I understand exactly what you want. If I had to guess, it sounds like you want a line chart rather than a scatter chart for this. I'm guessing this based on comments like "tried to change the Type to Text but not available in Excel 2016 that I can find." A text category horizontal axis is not a feature of a scatter chart, which requires a numeric horizontal axis. A line chart, on the other hand, is perfectly happy to accept a text/category horizontal axis. Again, I'm reading between the lines here, but I'm wondering if what you are looking for is a line chart instead of a scatter chart.

Not sure I can make this any clearer but I'll try...
Chart populates extra dates on front and back and weekend/holiday dates which are not part of the values presented in A3:A17 when min/max are not pushed through VBA.
With the approach you've provided - which I've already tried - I'm still getting the weekends and holidays as blank entries on the chart (e.g. blank space above the dates)
and I am trying to eliminate this clutter.
The only dates which should appear are those in the chart which exclude weekends and holidays.
All attempts have gotten me closer but nothing is removing those entries.

Please Login or Register  to view this content.

A variation on your approach, clears off the extraneous beginning and ending dates but does nothing to remove the internal weekends and holidays between the 15 days presented.
The min/max become hard layers that do not use the chart dates but execute the values based on the total range between the dates provided violating the intent of just using the charts data.

Other approaches without the defined min/max have given me results without these dates but I then end up with the min / max undefined and there are blank dates added before and after the dates in the data.

Bottom line, I just want the specific dates which will be a maximum of 15 events but can be less depending on the value in D54.
To also clarify, Line/Scatter/Bar it doesn't matter, I'm still getting garbage blank entries.

I don't have access to Excel, so I cannot test. Again, assuming I'm understanding correctly, I would expect to:

1) Change chart type from scatter to line.
2) Format horizontal axis to be text (not date).
3) If necessary, use dynamic named ranges for the chart ranges to avoid extra "blank" categories when the table is not filled.

It sounds like you have tried many different things. Have you tried that combination?

You might get more traction in the VBA section - shall I move the thread for you?

I've moved it. You don't need to go anywhere else - just continue here.

I guess I would have to see what you have tried to see why you are getting blank entries. I also think, before worrying about programming anything different (whether in VBA or however we choose to interact with the spreadsheet/chart), we need to understand exactly what settings and such we need to apply to the chart.

I just don't understand how you are getting blank categories, and your sample file (which uses a scatter chart) doesn't show me how you are arriving at blank categories. With two edits ( (1) change chart type from scatter to line and (2) change axis type from automatic to text), I get this chart. Note that there are no blank categories to the left, right, or in the middle.

At this point, I cannot see where your blank categories are coming from, so I'm not sure what edits to suggest for your code (though I might not be the best to suggest edits, since I almost never interact with Excel charts through VBA). I'm hopeful that, once we understand where your extra categories are coming from, the VBA edits will become obvious.

When I opened your file, the first thing I did was check the chart type and axis type. I see that you are using a line chart and that the axis is set to auto detect the chart type (and Excel has chosen a date axis for you). If I click on the button next to "Text" to force the axis to be a text/category axis, the extra dates disappear.

If I haven't adequately emphasized it, you must tell Excel that you want a text axis when formatting the horizontal axis in order to prevent Excel from using a date axis that will show "extra" dates.

I've reread through everything, and I'm not sure what I'm missing or how my suggestion to use a line chart with a text axis fails to address the existence of extra dates. In both of your sample files, if I change the chart type to line (in the file in post #1) and change the axis type to text (in both files), the chart doesn't show the extra dates. If that is not working for you, then I must be misunderstanding something.

Can you upload a sample file where the chart type is line and the axis type is text that is showing the extra dates? At this point, I'm not sure how much more I can help until I understand the problem, which appears to mean that I need to see an example of a line chart with a text axis displaying extra dates.