If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autofilter includes extra row
Just recently Autofilter has started including the last row of data in the
sheet, even though this row does NOT contain the filter criteria. Anything I can do? George Gee |
#2
|
|||
|
|||
Autofilter includes extra row
Do you have a hidden blank row or a very narrow blank row above the last
row? Deal with that. Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 19:16:09 +0100, "George Gee" wrote: Just recently Autofilter has started including the last row of data in the sheet, even though this row does NOT contain the filter criteria. Anything I can do? George Gee |
#3
|
|||
|
|||
Autofilter includes extra row
Filter your data once more.
Now take a look at the row numbers. You'll notice that the range that was filtered (and now visible) has blue row numbers. I'm guessing that that last row number is still grey. That means that excel doesn't see it as part of the autofilter range. You could remove the filter arrows and select the entire range and reapply the filter to see if that works. === Just a warning. I had some code that created a new workbook that a filtered sheet in it. Sometimes (always???), the lastrow wasn't included in the filter range. I ended up adding a new last row and filling it with bogus data. I applied the data|filter|autofilter and then emptied that row with the bogus entries. Sometimes, when the filtering worked perfectly, I'd get "blanks" as an option when the real data didn't contain blanks in that field. But I learned to live with that. (I think that was xl2k, too.) George Gee wrote: Just recently Autofilter has started including the last row of data in the sheet, even though this row does NOT contain the filter criteria. Anything I can do? George Gee -- Dave Peterson |
#4
|
|||
|
|||
Autofilter includes extra row
Hi Gord, no hidden or narrow rows in the sheet.
Hi Dave, all is as you say re row number colours. I removed the autofilter arrows and re-selected the range with the same result. However, I've solved the problem. I deleted the last row, inserted a new row in a purely random position, copied the deleted row data from a copy of the workbook, and pasted it in the new row. I then sorted the sheet to my requirements, and all is working OK! I don't pretend to understand what happened, but at least it's back to normal. Many thanks George Gee "Dave Peterson" wrote in message ... Filter your data once more. Now take a look at the row numbers. You'll notice that the range that was filtered (and now visible) has blue row numbers. I'm guessing that that last row number is still grey. That means that excel doesn't see it as part of the autofilter range. You could remove the filter arrows and select the entire range and reapply the filter to see if that works. === Just a warning. I had some code that created a new workbook that a filtered sheet in it. Sometimes (always???), the lastrow wasn't included in the filter range. I ended up adding a new last row and filling it with bogus data. I applied the data|filter|autofilter and then emptied that row with the bogus entries. Sometimes, when the filtering worked perfectly, I'd get "blanks" as an option when the real data didn't contain blanks in that field. But I learned to live with that. (I think that was xl2k, too.) George Gee wrote: Just recently Autofilter has started including the last row of data in the sheet, even though this row does NOT contain the filter criteria. Anything I can do? George Gee -- Dave Peterson |
#5
|
|||
|
|||
Autofilter includes extra row
Your workaround is as nutty as the one I used.
Here's hoping that it doesn't break for you. George Gee wrote: Hi Gord, no hidden or narrow rows in the sheet. Hi Dave, all is as you say re row number colours. I removed the autofilter arrows and re-selected the range with the same result. However, I've solved the problem. I deleted the last row, inserted a new row in a purely random position, copied the deleted row data from a copy of the workbook, and pasted it in the new row. I then sorted the sheet to my requirements, and all is working OK! I don't pretend to understand what happened, but at least it's back to normal. Many thanks George Gee "Dave Peterson" wrote in message ... Filter your data once more. Now take a look at the row numbers. You'll notice that the range that was filtered (and now visible) has blue row numbers. I'm guessing that that last row number is still grey. That means that excel doesn't see it as part of the autofilter range. You could remove the filter arrows and select the entire range and reapply the filter to see if that works. === Just a warning. I had some code that created a new workbook that a filtered sheet in it. Sometimes (always???), the lastrow wasn't included in the filter range. I ended up adding a new last row and filling it with bogus data. I applied the data|filter|autofilter and then emptied that row with the bogus entries. Sometimes, when the filtering worked perfectly, I'd get "blanks" as an option when the real data didn't contain blanks in that field. But I learned to live with that. (I think that was xl2k, too.) George Gee wrote: Just recently Autofilter has started including the last row of data in the sheet, even though this row does NOT contain the filter criteria. Anything I can do? George Gee -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|