Autofilter misses last line
 

I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

Answer: Autofilter misses last line
 

Hi Richard,

It sounds like you're experiencing an issue with the autofilter function in Excel. Here are a few things you can try to resolve the issue:

1. Check for hidden rows: It's possible that there are hidden rows in your dataset that are causing the issue. To check for hidden rows, select the entire dataset by clicking on the top-left corner of the worksheet (where the row and column headers meet), then go to Home Cells Format Hide & Unhide Unhide Rows. If any rows were hidden, they will now be visible and you can try applying the autofilter again.
   
2. Check for blank cells: Even though you've double-checked that there are no empty cells in the last row, it's possible that there are blank cells in other rows that are causing the issue. To check for blank cells, select the entire dataset by clicking on the top-left corner of the worksheet, then go to Home Editing Find & Select Go To Special Blanks. If any blank cells are found, you can either delete them or fill them with a value.
   
3. Use a table: If you're not already using a table to manage your dataset, try converting it to a table by selecting the entire dataset and going to Insert Table. Tables have built-in filtering capabilities that may work better than the autofilter function.

I hope one of these solutions works for you. Let me know if you have any other questions or if there's anything else I can help with.

Autofilter misses last line
 

Excel tries to help by using colors.

If you filter a field, then that arrow will turn blue.

Your row numbers in that filtered range will turn blue if the data is filtered.

My bet is that last row number is still black.

That means that this row isn't part of the filtered range.

I'd remove the filter and select the whole range and reapply the filter.



Richard wrote:

I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

--

Dave Peterson

Autofilter misses last line
 

You're right that the 2075th row is not blue.
However, even if I hand-select that data base (including row 2075) before
selecting Autofilter, it still keeps row 2075 no matter what technical area I
select.
--
Richard


"Dave Peterson" wrote:

Excel tries to help by using colors.

If you filter a field, then that arrow will turn blue.

Your row numbers in that filtered range will turn blue if the data is filtered.

My bet is that last row number is still black.

That means that this row isn't part of the filtered range.

I'd remove the filter and select the whole range and reapply the filter.



Richard wrote:

I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

--

Dave Peterson

Autofilter misses last line
 

Richard,

I have also seen a similar problem where the spreadsheet will sort all bar
the last few lines. If you insert data within the spreadsheet it will be
included with anything like sorting. However, data added to the end is not
always included. I've got round it by changing the print area (Click on
<Insert<Name<Define<Print_Area). I've not had this problem too many
time so I haven't been able to study it so I'm not sure what causes the
problem or why that should work and there may be a better way out of it.

Hope this helps.

Bill Ridgeway
Computer Solutions

"Richard" wrote in message
...
I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then
xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

Autofilter misses last line
 

Bill,
Adjusting the print area didn't help.
However, when I selected all the data plus one blank row, it did include the
'real' last row (2075) in the filtering. Only problem is that it now as a
filter option listed as 'blanks' - which is not too bad of a problem.
--
Richard


"Bill Ridgeway" wrote:

Richard,

I have also seen a similar problem where the spreadsheet will sort all bar
the last few lines. If you insert data within the spreadsheet it will be
included with anything like sorting. However, data added to the end is not
always included. I've got round it by changing the print area (Click on
<Insert<Name<Define<Print_Area). I've not had this problem too many
time so I haven't been able to study it so I'm not sure what causes the
problem or why that should work and there may be a better way out of it.

Hope this helps.

Bill Ridgeway
Computer Solutions

"Richard" wrote in message
...
I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then
xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

Autofilter misses last line
 

Richard,

having done that, you could now highlight row 2076 (the blank row) and
Edit | Delete to remove it. That should reset your filter range so that
you do not get "Blanks" listed in the drop-down.

I suspect that you might have two cells somewhere that are merged
together - Excel thinks that there are only 2074 cells (and therefore
rows) in the filter range.

Hope this helps.

Pete

Richard wrote:
Bill,
Adjusting the print area didn't help.
However, when I selected all the data plus one blank row, it did include the
'real' last row (2075) in the filtering. Only problem is that it now as a
filter option listed as 'blanks' - which is not too bad of a problem.
--
Richard


"Bill Ridgeway" wrote:

Richard,

I have also seen a similar problem where the spreadsheet will sort all bar
the last few lines. If you insert data within the spreadsheet it will be
included with anything like sorting. However, data added to the end is not
always included. I've got round it by changing the print area (Click on
<Insert<Name<Define<Print_Area). I've not had this problem too many
time so I haven't been able to study it so I'm not sure what causes the
problem or why that should work and there may be a better way out of it.

Hope this helps.

Bill Ridgeway
Computer Solutions

"Richard" wrote in message
...
I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then
xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

Autofilter misses last line
 

I've only seen a couple of worksheets that had this problem--that the last row
wouldn't be included in the autofilter range.

My workaround was to add something to a cell under that row. Select the whole
range--including that dummy row and apply data|filter|autofilter to that
extended range.

Then I'd clear the contents of that dummy cell.

Yes, I usually ended up with an extra blank line in my autofilter range.

But I could live with that.

Richard wrote:

You're right that the 2075th row is not blue.
However, even if I hand-select that data base (including row 2075) before
selecting Autofilter, it still keeps row 2075 no matter what technical area I
select.
--
Richard

"Dave Peterson" wrote:

Excel tries to help by using colors.

If you filter a field, then that arrow will turn blue.

Your row numbers in that filtered range will turn blue if the data is filtered.

My bet is that last row number is still black.

That means that this row isn't part of the filtered range.

I'd remove the filter and select the whole range and reapply the filter.



Richard wrote:

I have a 2075 row database that I autofilter by technical area
(i.e. Data/Filter/Autofilter on technical area column header).

The filter works fine, except no matter what technical area I select from
autofilter pulldown, the last row always displays.

I have double checked that there are no empty cells in the last row.
I have also verified that there are no empty cells in the last row by
writing a short macro that starts on column A of the last row, then xltoRight
(i.e. Range("a2075", Range("a2075").End(xlToRight)).Select does indeed
select all the cells in the data set)

Any ideas why the last row doesn't my autofilter apply to the last row?
--
Richard

--

Dave Peterson


--

Dave Peterson

Autofilter misses last line
 

When the last line of data isn't included in the filtered range, it's
often because the row contains a SUBTOTAL formula.

Richard wrote:
Bill,
Adjusting the print area didn't help.
However, when I selected all the data plus one blank row, it did include the
'real' last row (2075) in the filtering. Only problem is that it now as a
filter option listed as 'blanks' - which is not too bad of a problem.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html