Rows not being Included in Table Filter

[HWScott]

Hello, thanks in advance to anyone who helps with this issue.

I am having trouble with a date column - some dates in August 2017 are appearing at the bottom of my table, no matter how I filter.
Spreadsheet is attached.

I've Googled half the day away and tried Text to Columns, looking for blank rows, etc.
Any advice would be greatly appreciated. Thanks!

[MarvinP]

Hi,

I see your workbook with 8/1/2017 at the bottom and you've filtered out 2017. When I filter out 2017 that date goes away (is filtered out). Try to filter it a second time and if there are still problems look for these:

1. It isn't being treated as a number/date but text. See if any of the 8/1/2017 have a single quote in front of them, making them text.
2. The table range might not span all the way down, so the last date isn't included in the table.

Those are my two hints, but it worked for me without problems. What version of excel are you using?

[HWScott]

MarvinP,

No luck here. My version is 2010.
In fact, when I turn the filter off and filter 2017 out again, an additional row from August 2017 stays at the bottom.
Although the entirety of the rows are inside the table range, only the dates before August 2017 have blue row #s (signifying that they are part of a filtered range)...

I'm baffled!

[MarvinP]

Is there a blank row just above your last Aug 1 2017?

[sandy666]

With your example....

All your dates are Excel dates (yellow)
I tried some different ways to get "error" but no success

I add one more hint: don't use bells&whistles before your project comes to an end
I mean formatting from the ribbon or custom format.

eg. you don't know your dates are text or number because they are aligned (manually) to the center. Real Excel date is aligned to the right because this is the number. etc.. etc..

maybe RAM is too low try refresh few times (Shift+F9)

Edit:
I see "error" if I download attachment but if I simply open filter on any column and click ok (nothing more) "error" disapperas.

[HWScott]

Is there a blank row just above your last Aug 1 2017?

Nope.

With your example....

I see "error" if I download attachment but if I simply open filter on any column and click ok (nothing more) "error" disapperas.

Yeah, I'm still experiencing this issue. Thanks to those who have chimed in so far. Any input welcome. =)

[JeteMc]

I can't tell you what is causing the issue, but I can tell you what I did to get the dates to filter (similar to what has been mentioned).
- I selected cell AE3129 and pressed the tab key (which caused the file to buffer for a while and the 'Save As' to pop up),
- I selected the filter in cell Z1 and pressed 'OK'.
The table then filtered as I would expect (showing only the rows through 2488).
Let us know if you have any questions.

[sandy666]

Mybe it will be stupid idea but try (after click OK on any filter, save and close) to zip this file, unzip, and check for this error again.
Zip, Rar, whatever....

[MarvinP]

Fantasy!

[HWScott]

Thanks again for your time, folks.

I can't tell you what is causing the issue, but I can tell you what I did to get the dates to filter (similar to what has been mentioned).
- I selected cell AE3129 and pressed the tab key (which caused the file to buffer for a while and the 'Save As' to pop up),
- I selected the filter in cell Z1 and pressed 'OK'.
The table then filtered as I would expect (showing only the rows through 2488).
Let us know if you have any questions.

Unfortunately, this works temporarily until I allow 2017 and then try to filter it out again. A stubborn row remains.

Mybe it will be stupid idea but try (after click OK on any filter, save and close) to zip this file, unzip, and check for this error again.
Zip, Rar, whatever....

Also tried this, no luck.

[sandy666]

Maybe but if it will work what will you say?

[sandy666]

Did you try define new worksheet (workbook) with old data?

[HWScott]

Did you try define new worksheet (workbook) with old data?

That would work (just tested it out) as a last resort. Thanks for the suggestion.
The original sheet (with the sensitive information left in) is quite complex with tables, comments, pivots, etc.

[sandy666]

I tested it with new workbook and it works (no errors). Maybe it worth to do insted of sitting for a week and still have the same error