My data will not filter everything in a column, please help?

[Jyggalag]

Hi all,

I have this file that was converted from PDF to Excel.

I would really like to filter everything in column A by date

However, when I do it looks like this:

It filters down until row 687

The row looks like this:

I have no idea why it stops?

It used to stop at points like these as well:

It gets fixed if I (in the case above) delete row 948. I am not sure why, but this apparently blocks the filter from continuing down the column

However, my file has over 10.000 rows and it would take me ages to do this manually.

Does anybody have experience with this, or a nice solution simple solution to it, or both?

Would be highly appreciated!

Thank you all

Kind regards,
Jyggalag

 

[Jyggalag]

This is before I made any changes at all to the document except apply a filter btw:

Which then returns this:

So it already stops at row 94 or 96

 

[Alex Blakenburg]

If you only select 1 cell in your data range, Filter will use what is known as the Current Region to decide what to include in the filter area.
Current Region includes everything around that cell until it hits a Row that is empty (vertically) or a Column that is empty (horizontally).

To overcome this you need to manually select the "Entire area" you want to filter and not leave it up to Excel to automatically work it out.

If you have the original PDF file it is worth considering doing it all in Power Query. You can still use Power Query with what you have but you would again need to manually select the entire area and convert it to a Table so PQ knows what to include.

To Check what Excel is seeing as the current region, select a cell and press Ctrl+Shift+8 ( or Ctrl+*).

 

[Jyggalag]

T

If you only select 1 cell in your data range, Filter will use what is known as the Current Region to decide what to include in the filter area.
Current Region includes everything around that cell until it hits a Row that is empty (vertically) or a Column that is empty (horizontally).

To overcome this you need to manually select the "Entire area" you want to filter and not leave it up to Excel to automatically work it out.

If you have the original PDF file it is worth considering doing it all in Power Query. You can still use Power Query with what you have but you would again need to manually select the entire area and convert it to a Table so PQ knows what to include.

To Check what Excel is seeing as the current region, select a cell and press Ctrl+Shift+8 ( or Ctrl+*).

This is amazing!!

It worked! I just hit CTRL SHIFT + arrow movements and selected everything and then pressed DATA --> FILTER

Thank you so much Sir!

I'm still a bit lost as to why it does this, as I have had tables with empty cells before, where it did not stop the filter from working, but happy to hear this and it worked, so thank you very much!

 

[Alex Blakenburg]

Perhaps previously although you had empty cells in column you working with, the rows of those cells weren't entirely empty and had something in columns that formed part of the current region.

If you are interested watch the first 1 minute of this.

Anyway glad you have it working now. Thanks for letting me know.