Autofilter Bug

[adgish]

Hi guys,

I recently ran into a complicated problem with the autofilter in Excel (specifically its interaction with data series and scatterplot charts). After experimenting with it more, I now understand the problem but still have no solution. Here is what I know:

When data is autofiltered, the series containing it is temporarily “cropped” down to a size that contains only data points that meet the requirements of the filter. Thus, only the filtered points are shown on a scatterplot chart.

However, although the series is shortened, Excel doesn’t pick and choose the points to cut out, it simply chops the correct number off the end of the series. This means that points which meet the requirements of the filter are shown in the correct spot on a scatterplot, but their markerstyles and labels correspond to other points.

For example, imagine a 10 point series with data labels 1 through 10. If I filter out points 8 and 9, they will show up in the correct position on my scatter plot chart, but they will be labeled 1 and 2 respectively. Also, they will have the markerstyles of points 1 and 2, rather than their own.

I need the data points to maintain their correct data labels and markerstyles(symbols) even when filtered. I would appreciate any insight you can offer.

-Andrew

 

[SkipVought]

Hi,

Could you send me an example. You may eMail an abbreviated portion of your Excel file if you choose.

What version of Excel are you using?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Pending a more problem-specific solution from Skip, let me kick in a general suggestion.

Often, the simplest workaround to the various issues caused by trying to work on filtered data is just to "bounce" the visible rows from your filtered data to a hidden sheet, and work with those rows.

So your scatterplot chart would be pointed to the hidden sheet, which would contain your data and datalabels.

It's a bit more work up front, but it saves you from having to find another workaround to the next bug you discover while trying to operate on filtered data. . .

VBAjedi

 

[SkipVought]

and sometimes the simplest of solutions, beats the pants off the most elegant! Good tip, VBAjedi

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

I do a lot of nutty stuff with filtering, so I learned that approach the hard way. . .

Andrew, let us know if this approach works for you!

VBAjedi

 

[xlbo]

And hey, if you're gonna go with bouncing a copy, may as well use AdvancedFilter while you're at it

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[adgish]

Bouncing the data would certainly be easier than writing a macro to pull the numbers out of the chart. However, if I did that I would need to reset the values of the series each time I changed the filter. The problem is, a loop that changes the markerstyles of 1000 data points can take several minutes. eg:

For Each pt In Charts("MyMap&quot.SeriesCollection("ForegroundPins&quot.Points
DuplicateSeries.Points(Counter).MarkerStyle = pt.MarkerStyle
pt.DataLabel.Font.Size = 2
Counter = Counter + 1
Next pt

(This process matches the markerstyles of one series to another)
Any ideas on how to speed this process up?