Changing what Auto filter does

[rob nobel]

With auto filter, when selecting an item from its dropdown list, all the
data is sorted according to that selection. The problem I have is that next
to the column I'm sorting, there are some columns that have formulas in
them. Unfortunately, in the sort, all the rows that have the formulas in
them are "hidden" by the sort procedure even if there is no data in those
cells. The formulas I have return a true or false, but I notice that even
the simplest of formulas will cause the same thing to occur.
Is there a way to over-ride this?

The other strange thing about this is that if I delete the formulas it
doesn't fix the problem until I use the Delete option on the Edit menu on
the toolbar.
I tried this on a blank workbook by having 3 headings on row 1 and about 3
rows of data under them. Then next to those columns a simple formula like
=A2+B2, etc. Keeping the formulas to just the 3 rows the filter behaves
properly.
If the formula is copied down further, the data is sorted and the next
available row becomes the row under the last formula. Deleting the formulas
doesn't return the filter back to what it did before adding the extra rows
of formulas.
Is this normal, or just my PC?
Rob

 

[Gord Dibben]

Rob

To start, Autofilter does not "sort" data.

It just hides entire rows that do not meet your criteria. The visible rows
are still in the order in which they were originally.

This is by design.

Can't figure out what else you want Autofilter to do for you.

Gord Dibben Excel MVP

 

[rob nobel]

Gord, I may not have been too clear on this. I'm sorry, wrong terminology.
In sorting I mean it sorts out everything other than the item you select
revealing only items that meet the criteria. I realise it doesn't sort
alphabetically or similar and that's not what I want.
If you could read my problem again and accept that I have used the word
"sort" to mean, show the selected criteria and hide the rest, then I would
still like to know how to resolve this if possible.

Rob

 

[Dave Peterson]

It sounds as though those formula cells are not part of your autofilter range
and you want to see them when your table is filtered.

Excel hides the entire row when filtering (or even hiding rows manually).

If I guessed right, one solution is to put those formula cells under the range
that will be filtered.

If I guessed wrong (a good possibility), post back with more description.

 

[Gord Dibben]

Apologies Rob. I can't get my head around what you're referring to.

Autofilter hides all rows that don't meet the criteria.

This includes all columns in those hidden rows.

Sounds like you want to filter just some columns and leave others un-filtered.

Excel won't do that.

Gord

 

[rob nobel]

Sorry about your head Gord, I'll try and explain it some more. (It's making
mine spin too!)

If I'm filtering column B (which has 10 rows of data) and I filter it
depending on certain criteria, after the filter is applied, all the rows
that do not meet the criteria are hidden. So.... if the value in row 10
held the criteria, then row 11 would be the next blank row.
But if there are formulas in column A down to row 20, then the next row at
the bottom of the filtered data becomes row 21.

What I would like to occur is for the filter process of Column B to ignore
the fact that there are formulas in column A. Is this possible?
Why I need this is that I want to filter column B and then add some stuff in
the row directly below the last cell in column B.
BUT.....the next row could be some 1,000 rows down from that if the formulas
in Column A extend that far down.
Hope this helps.
Rob

 

[rob nobel]

Thanks for your reply Dave. That's correct, that column with the formulas
is not part of the filter range, but I don't want to see them either
(they're actually in hidden columns). That's not the problem.
I just saw this reply from you after I wrote back to John.
This is what I wrote in a hope for clarification.....

If I'm filtering column B (which has 10 rows of data) and I filter it
depending on certain criteria, after the filter is applied, all the rows
that do not meet the criteria are hidden. So.... if the value in row 10
held the criteria, then row 11 would be the next blank row.
But if there are formulas in column A down to row 20, then the next row at
the bottom of the filtered data becomes row 21.

What I would like to occur is for the filter process of Column B to ignore
the fact that there are formulas in column A. Is this possible?
Why I need this is that I want to filter column B and then add some stuff in
the row directly below the last cell in column B.
BUT.....the next row could be some 1,000 rows down from that if the formulas
in Column A extend that far down.
Hope this helps.
Rob

 

[Dave Peterson]

I think you have two problems.

First, you replied to Gord--not John!!!

Second, you're fighting excel. It knows better than you what your autofilter
range should be. It _knows_ that you really want to include column A in that
range.

My suggestion is to give up fighting excel. Insert a new column B and leave it
completely empty. Then apply your filter to C1:c10.

I use that same technique to give into excel when I want to limit the rows in
the autofilter--I insert an empty row and reapply my autofilter.

 

[rob nobel]

Hi Dave, Thanks again.
Oops! Sorry!
I think I've probably not explained it or can't quite understand what you're
suggesting. If I explain exactly the layout, maybe that will help as I
already have a blank column next to the column I want to filter.
1. the rows to filter start at row 26 with headers in 25 and data extends to
about row 475 at the moment. Auto filtering is applied to Row 25 Col F to
Q.
2. All the rows above 26 are frozen and locked
3. Auto filtering is applied to Row 25, Col F to Q. (Which have headers)
4. Columns A and B have formulas in them that produce True or False down to
row 1525
5 Column C is blank (from row 26 down)
6. Column D has formulas down to row 1525
7. Column E has some data down to Row 475
If I select a criteria in col F, the next row immediately after the filtered
data is row 1526.
As Row 475 is the last row that actually holds data From Col D to Q, I would
like the filter not to filter out all the rows from 475 to 1525 but this
seems to be a problem if there are formulas in the other columns.

Rob

 

[Dave Peterson]

I'm gonna stick with an answer that's very similar. Isolate your range that you
want filtered.

Put an empty column between E and F.

I still think that excel knows better than you! It's kind of like hitting
ctrl-* (edit|goto|special|current region). Excel just wants to extend that
range whether you like it or not. (I don't think it's the columns--copy|paste
special|values and see if that helps (but don't save!!!).


Try this after you experiment a little:

MsgBox ActiveSheet.AutoFilter.Range.Address

I set up a test worksheet like you described (too lazy to make it exactly the
same number of rows, though!)

I ran this:
Debug.Print ActiveSheet.AutoFilter.Range.Address
Range("f1").EntireColumn.Insert
Debug.Print ActiveSheet.AutoFilter.Range.Address

And I got this:
$F$25:$Q$1688
$G$25:$R$475

(offset by one column and the rows adjusted--I did do the 475 correctly!)

Hi Dave, Thanks again.
Oops! Sorry!
I think I've probably not explained it or can't quite understand what you're
suggesting. If I explain exactly the layout, maybe that will help as I
already have a blank column next to the column I want to filter.
1. the rows to filter start at row 26 with headers in 25 and data extends to
about row 475 at the moment. Auto filtering is applied to Row 25 Col F to
Q.
2. All the rows above 26 are frozen and locked
3. Auto filtering is applied to Row 25, Col F to Q. (Which have headers)
4. Columns A and B have formulas in them that produce True or False down to
row 1525
5 Column C is blank (from row 26 down)
6. Column D has formulas down to row 1525
7. Column E has some data down to Row 475
If I select a criteria in col F, the next row immediately after the filtered
data is row 1526.
As Row 475 is the last row that actually holds data From Col D to Q, I would
like the filter not to filter out all the rows from 475 to 1525 but this
seems to be a problem if there are formulas in the other columns.

Rob

 

[rob nobel]

Thanks for persisting with me Dave. I did rethink your earlier post and
decided to create a new workbook to try that on and it works fine.
Unfortunately I cannot get it to work on the wkbk in question. Even if I
highlight the whole sheet, copy and paste special>Values, (eliminating all
formulas), and also setting the code on the sheet to break mode, it still
doesn't do it, no matter how many times I cancel and reapply the auto filter
or add blank columns. Nor does removing freeze panes, protection or
formatting change anything. In fact, I deleted all the rows before row 25
and inserted some new ones.
I fear I've got a "dirty" spreadsheet which I would normally just delete,
but as there are so many references to it including buttons and other stuff,
it may be a task I don't want to start.
I also tried your little bit of code which also did nothing to fix the
problem (which I know was not intended) and got $K$25:$V$1525, so the range
obviously still goes down to row 1525.
I don't know what to do now except maybe delete columns/rows progressively
until I find what may be causing this problem.
This is the second time I've had a problem with Excel having a memory!!
(Although it's probably not the memory but something else.)
I wonder if this software can have a stroke so we don't have to put up with
it thinking it knows better. Maybe MS can give an option in future program
versions to allow the user to determine which memory we'd like to use.

Rob

 

[Dave Peterson]

First, that snippet of code wasn't trying to fix anything. It was just
displaying the addresses of the filtered range.

After you inserted that empty column, how did you apply the filter? Did you
select the columns and let excel guess or did you select the exact range and
then apply the filter.

If you let xl guess the range, try it the other way.

If I selected the whole column(s), I could reproduce your problem. If I
selected the exact range, it worked fine.

I almost always select the range when I'm doing autofilter, sorts,
pivottables....

I don't like to let excel guess at anything.

Thanks for persisting with me Dave. I did rethink your earlier post and
decided to create a new workbook to try that on and it works fine.
Unfortunately I cannot get it to work on the wkbk in question. Even if I
highlight the whole sheet, copy and paste special>Values, (eliminating all
formulas), and also setting the code on the sheet to break mode, it still
doesn't do it, no matter how many times I cancel and reapply the auto filter
or add blank columns. Nor does removing freeze panes, protection or
formatting change anything. In fact, I deleted all the rows before row 25
and inserted some new ones.
I fear I've got a "dirty" spreadsheet which I would normally just delete,
but as there are so many references to it including buttons and other stuff,
it may be a task I don't want to start.
I also tried your little bit of code which also did nothing to fix the
problem (which I know was not intended) and got $K$25:$V$1525, so the range
obviously still goes down to row 1525.
I don't know what to do now except maybe delete columns/rows progressively
until I find what may be causing this problem.
This is the second time I've had a problem with Excel having a memory!!
(Although it's probably not the memory but something else.)
I wonder if this software can have a stroke so we don't have to put up with
it thinking it knows better. Maybe MS can give an option in future program
versions to allow the user to determine which memory we'd like to use.

Rob

 

[rob nobel]

Hi Dave,
I did point out that I knew your code didn't do that. I was just hoping
that because it added a column that it might have done something.
By some stroke of luck the problem has resolved itself. Not sure why as all
I did was progressively delete columns until the filter did what it should.
Anyway, one column, I think about 6 or 7 to the right of the column I was
trying to filter ,(and was originally one of the columns normally filtered,
had some data in occasional rows. When I deleted all that data (some were
just zeros), the filter process worked fine?????

Secondly, I always select the exact headers. (I don't like Excel deciding
for me.)
Are you suggesting that you should highlight the full range including
headers and data, as if I do that on the data now (having been fixed), I can
replicate the problem again.

Rob

 

[Dave Peterson]

Sorry, I misunderstood the code reference. (I hope no one (Deb!) is counting.)

But yeah, I almost always select the bottom right to top left cell--the exact
range.

Shhhh....Sometimes, when I know the data (headers in row 1 and data in row 2
down and nothing else), I'll just highlight the columns to filter.

But those are usually just for temporary filters. If I'm doing it for keeps
(someone else's project), I'll select the exact range.

That might have been the fix that you stumbled onto when you were trying
everything.

Hi Dave,
I did point out that I knew your code didn't do that. I was just hoping
that because it added a column that it might have done something.
By some stroke of luck the problem has resolved itself. Not sure why as all
I did was progressively delete columns until the filter did what it should.
Anyway, one column, I think about 6 or 7 to the right of the column I was
trying to filter ,(and was originally one of the columns normally filtered,
had some data in occasional rows. When I deleted all that data (some were
just zeros), the filter process worked fine?????

Secondly, I always select the exact headers. (I don't like Excel deciding
for me.)
Are you suggesting that you should highlight the full range including
headers and data, as if I do that on the data now (having been fixed), I can
replicate the problem again.

Rob

 

[rob nobel]

Darn it!
The problem is back! It appears that if I have any formulas in the same
columns underneath the data, the auto filter hides those rows as well, no
matter what I do. The only thing that stops this (other than deleting the
formulas) is to insert a row at the end of the data. That is totally
unsatisfactory as the wkbk I'm trying to create is supposed to be a "user
friendly" wkbk where most things are automated except for entering data.
Cuss, cuss cuss!!!
Is there any way whereby you can sort data that doesn't hide the rows at the
end that contain formulas, (without the user having to manipulate the
data/rows in any way)?

Rob

Hi Dave,
I did point out that I knew your code didn't do that. I was just hoping
that because it added a column that it might have done something.
By some stroke of luck the problem has resolved itself. Not sure why as all
I did was progressively delete columns until the filter did what it should.
Anyway, one column, I think about 6 or 7 to the right of the column I was
trying to filter ,(and was originally one of the columns normally filtered,
had some data in occasional rows. When I deleted all that data (some were
just zeros), the filter process worked fine?????

Secondly, I always select the exact headers. (I don't like Excel deciding
for me.)
Are you suggesting that you should highlight the full range including
headers and data, as if I do that on the data now (having been fixed), I can
replicate the problem again.

Rob

First, that snippet of code wasn't trying to fix anything. It was just
displaying the addresses of the filtered range.

After you inserted that empty column, how did you apply the filter? Did you
select the columns and let excel guess or did you select the exact range and
then apply the filter.

If you let xl guess the range, try it the other way.

If I selected the whole column(s), I could reproduce your problem. If I
selected the exact range, it worked fine.

I almost always select the range when I'm doing autofilter, sorts,
pivottables....

I don't like to let excel guess at anything.

if

I

row

25

False

down

Q,

I

but

this

B

and

limit

the

all

the

B

to

if

the

no

data

row

1

adding

 

[rob nobel]

Thanks Dave, I seem to have lost the proper order of this thread and
answers seem to be out of sync. Forgive me, as my daughter got married this
WE and I've had a houseful of women, which, with all the hair spray and
fingernail polish has left my brain partially paralysed.
Nevertheless, I'm afraid the problem seems to be more than I can cope with.
I thought I had it licked, but NO!
No matter if I add columns, rows or whatever, I cannot get the autofilter to
ignore cells below the last of the data. Unless of course I separate the
last of the data and the following rows with formulas with the insertion of
a blank row. But that just won't do, as all this needs to be automated and
getting a macro to do all that and delete the row again when selecting "Show
All" in the filter dropdown seems to be an arduous way to go.
Can Advanced filter do this better?
Why I need this procedure to work is that the wkbk is, amongst other things,
an accounts wkbk where the user will select a bank account and by doing so,
all the other bank accounts details are hidden. The user then marks off
against each item that appears on the bank statement, thus balancing the
account. The problem is that sometimes there are items on the bank
statement that have not been entered in the wkbk, such as auto deductions or
bank charges, and these will be added whilst the sheet is in filter mode.
So... if all the rows under the last data entry are hidden, the user cannot
enter those details.
For a few reasons I guess I've made all this a bit difficult as I have made
the one worksheet handle all 8 bank account and in trying to hide the other
accounts I've encountered this problem.

Rob

 

[Dave Peterson]

I'd leave the extra column and the extra row as separators.

When you tested the Advanced Filter, did it do what you want? I didn't test it.

Couldn't you keep that separator row and then do a sort to put it back into a
contiguous range whenever you wanted? Maybe put a button on the worksheet that
does the work?

Thanks Dave, I seem to have lost the proper order of this thread and
answers seem to be out of sync. Forgive me, as my daughter got married this
WE and I've had a houseful of women, which, with all the hair spray and
fingernail polish has left my brain partially paralysed.
Nevertheless, I'm afraid the problem seems to be more than I can cope with.
I thought I had it licked, but NO!
No matter if I add columns, rows or whatever, I cannot get the autofilter to
ignore cells below the last of the data. Unless of course I separate the
last of the data and the following rows with formulas with the insertion of
a blank row. But that just won't do, as all this needs to be automated and
getting a macro to do all that and delete the row again when selecting "Show
All" in the filter dropdown seems to be an arduous way to go.
Can Advanced filter do this better?
Why I need this procedure to work is that the wkbk is, amongst other things,
an accounts wkbk where the user will select a bank account and by doing so,
all the other bank accounts details are hidden. The user then marks off
against each item that appears on the bank statement, thus balancing the
account. The problem is that sometimes there are items on the bank
statement that have not been entered in the wkbk, such as auto deductions or
bank charges, and these will be added whilst the sheet is in filter mode.
So... if all the rows under the last data entry are hidden, the user cannot
enter those details.
For a few reasons I guess I've made all this a bit difficult as I have made
the one worksheet handle all 8 bank account and in trying to hide the other
accounts I've encountered this problem.

Rob

 

[rob nobel]

Dave, I can't leave the extra row as the data keeps growing and it would
complicate matters in other procedures.

I can't get the Advanced filter to do anything at all on this particular
sheet UNLESS I select Unique items only and then, all it does is leave the
data as is, including the ones with formulas, but hides all the rows that do
not have any formulas.

My last few strands of hair are starting to tremble.

The method I used:
I inserted 2 blank rows above the header row and selected advanced filter,
selected the full range G28:R1525 (not including the headers) and chose the
criteria as G16 which is one of the 8 names that fill column G which is the
column I'm trying to sort via autofilter.
Including the header row makes no difference at all.
I've also disabled the code on the sheet module which makes no difference
too.
Grrrrrr!

Rob

 

[Dave Peterson]

Maybe you could filter by the value you want OR blanks????

Or maybe you could use a macro (worksheet_change event) to add the formulas when
they're required.

I'm not sure I have any good way to satisfy all your requirements.

Dave, I can't leave the extra row as the data keeps growing and it would
complicate matters in other procedures.

I can't get the Advanced filter to do anything at all on this particular
sheet UNLESS I select Unique items only and then, all it does is leave the
data as is, including the ones with formulas, but hides all the rows that do
not have any formulas.

My last few strands of hair are starting to tremble.

The method I used:
I inserted 2 blank rows above the header row and selected advanced filter,
selected the full range G28:R1525 (not including the headers) and chose the
criteria as G16 which is one of the 8 names that fill column G which is the
column I'm trying to sort via autofilter.
Including the header row makes no difference at all.
I've also disabled the code on the sheet module which makes no difference
too.
Grrrrrr!

Rob

 

[rob nobel]

Hi Dave, I think I'm going to give up on this problem and try a new
approach. The possibility of adding the formulas is something I might
consider if I could only get the thing to work without the formulas, but now
it won't do that. This Excel memory is a real nuisance!!!
Oh well, thanks for your input.
Rob