Excel 2007 - AutoFilter BUG?!?

T

tasgolf72

New Member
Joined
Oct 26, 2007
Messages
15
Just wanting to see if anyone has ran into this problem in Excel 2007 yet. We have a large list of data with an autofilter on it. On column, R we want to show ONLY Blanks. Once we have the Blanks filtered, we put the word, TRADE (or any other word that you want). We finally select all the TRADE cell that were previously shown as blank and highlight them yellow. When we cancel the filter, all the rows in between are now highlighted yellow whereas in Excel 2003, only the rows that we highlighted when the filter was in place had the yellow highlighting.

There is a workaround that you can select each cell individually, apply a fill color, go onto the next cell, apply the color, etc but that is not efficient.

Any ideas?

Thanks!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Why not use conditional formatting to do the highlighting?
 
Upvote 0
Conditional Formatting

We could definately do that but just wondering if this is a known bug in Excel 2007 b/c it doesn't work that way in Excel 2003. It will only highlight what is shown when the filter is applied, not everything in between.
 
Upvote 0
I posted a similar problem with Excel 2007 a few weeks ago. The only reply I got was that it worked fine for them, which it does for me....occasionally!. This is a HUGE problem for me too. Can't use filters to delete rows, fill in blanks, copy cells to new worksheets, etc. because everything in between is effected too. Makes it pretty much wortless. I was begining to think it was only my problem (which of course, it what I think Microsoft wants us all to think). Wierd thing is is that is does work on very small data sets
 
Upvote 0
Bug?

I agree that it is hit and miss on different datasets. The problem is that I don't want to spend $100.00 to report the bug to Microsoft to have me tell them it's a bug and they are working on a fix (which will happen 2 years from now...).
 
Upvote 0
A SOLUTION!!!

Finally I found a workaround that someone else on the web has discovered for this bug in Excel 2007.

If you apply the filter for the first time, it will not work. So the workaround is to apply the filter, save the file, unapply the filter then re-apply the filter again. The 2nd time around on this filter tells it to behave like it always has in the past and only copy or apply to the filtered rows.

Good ole' Microsoft Bugs!!!
 
Upvote 0
I don't have XL2007. But is there not the equivalent of Edit | GoTo... | Special | Visible cells only ? I would think that having done that you could then apply color or delete rows. Again, I don't know how the whole "ribbon" thingamabob works, but on XL2003 you can customize a toolbar by adding visible cells only button to it. If this is, indeed what you have to do, you might wanna do something like that.

Just shootin' in the dark, really. But hope it helps,
 
Upvote 0
Yes

Yes there always is that option but I think our frustration stems from Microsoft not acknowledging that there is a bug in the way that it used to work.

Just tested some more and the easiest way to get it to perform like it did in the past is to apply the filter, cancel the filter, re-apply and it will work like normal - it's strange!
 
Upvote 0
Glad you've got it sorted.:)

But I still think you should consider an alternative method.

For example to get the blanks you could use, similar to what Greg suggested, special cells.

But instead of Visible cells only use Blanks.

Once you've highlighted the blanks enter TRADE with Ctrl+ENTER and then format as required.
 
Upvote 0
Selecting Visible Cells has its traps too, at least in 2007. Yes, it leaves the cells between the filter selection alone, but it does pick up cells below the filter selection (try putting a dummy value in cell A65000 for instance and doing a filter on some Column B value, copy, paste to another worksheet and the 65000th row will come through). Point is, you never really know what you are going to get. It depends on way too many things to have to anticipate every time you want to filter. Hard to believe that Microsoft didn't know about this bug before it wrapped it new package in pretty Ribbons!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Conditional Format to highlight dupes on EACH column BUT only on visible filtered selection
Replies
4
Views
188
felixstraube
F
GomaPile
  • Question
Find Function
4 5 6
Replies
58
Views
3K
Akuini
Akuini
I
  • Question
Before running code comment out another code
Replies
1
Views
194
Micron
M
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
227
skillilea
S
L
  • Solved
Conditional Formatting in Excel Table and Ignore Blanks Not Working
Replies
3
Views
262
Joe4
Joe4

Forum statistics

Threads
1,214,948
Messages
6,122,420
Members
449,083
Latest member
Ava19

Share this page

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
Back
Top