LinkBack URL
About LinkBacks
I tried to record a macro while using Advanced Filter, but it just doesn't use the criteria to filter it. All rows are returned. Anyone ever have this issue where advance filter keeps returning the entire row set?
Wyatt...
I tried to record a macro while using Advanced Filter, but it just doesn't use the criteria to filter it. All rows are returned. Anyone ever have this issue where advance filter keeps returning the entire row set?
Wyatt...
Hi, Wyatt,
sounds as if an empty row is included in the area for the criteria. Without us looking at the workbook itīs up to you to check by yourself.
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
is this enough info? I can copy entire workbook too?
Please Login or Register to view this content.
Thanks!
Wyatt...
Hi, Wyatt,
as far as I remember the area for the criteria must be the exact size in columns as the area you want to filter and should include the headers at least on a one to one base as they are shown in the data area. So I think the part
might be altered to readPlease Login or Register to view this content.
having row 1 as the headers and in row 2 the criteria for the filtering.Please Login or Register to view this content.
Please do not post your question about the same problem in different threads - http://www.excelforum.com/excel-prog...eria-ones.html.
Ciao,
Holger
sorry for the multiple threads, I thought this was more specific to the advanced filter problem.
I'm not sure what you mean by making criteria I1:L2 (why would it be two rows?) ...would that mean i should leave J1, K1 and L1 blank since it's only I1 that contains the criteria? Also, the actual column to check the criteria against is in column D (4th column) so would that mean I should re-jig my layout to put the criteria cell in L1?
Thanks!
Wyatt...
Hi, Wyatt,
if there isnīt confidential data in the workbook I think it would be best if you could attach that to have a look (or one sample with some data to play with).
How I use the Advanced Filter: I always have a header row for the data, and exactly that data is duplicated to the criteria as well as the output range for me. I know about the difficulties when filtering dates due to the different settings in the Operating System, maybe the times are treated accordingly.
Ciao,
Holger
OK! got it! thanks HaHoBe! just one minor snag that i'll deal with tomorrow, need sleep
Thanks again!
Wyatt...
Last edited by wyattea; 09-12-2013 at 03:10 AM.
Hi, Wyatt,
to me thatīs the "normal" behaviour of the Advanced Filter as the Data Range starts with the headers. What should work is to exclude the header row of the area to Filter. You should try and use Offset or alter the area to start with Row 2:
Ciao,Please Login or Register to view this content.
Holger
Hey Holger, can't sleepThis is racing through my mind...I tried the -1 but didn't work...I would think -1 would reduce the # of rows and leave off the LAST row, not the top one?
Here's my workbook if it helps to understand it better. The table that has grn/yellow/red fill is just conditional formatting to show when there's a number in C used once, twice and more than twice.
Wyatt...
The basic premise is a data dump occurs in the morning to EMPLIST-TODAY... on the Master DLR sheet, i would fill in staff based on shift time, which would conveniently appear to the right of the table. If i use a emp #, it highlights in grn, twice is yellow, red is more than 2 times. There's other boxes cause i'll add later options to search for skill or whatever, but for now it's just the box in L2 that works.
Problem solved, I just deleted the header i created for the area and will use the header that's being pull in, no problem
Thanks again HaHoBe!
Wyatt...
Hi, Wyatt,
glad to hear. If this problem is solved please mark the threasd as Solved via Thread Tools over the first post - you may revert that status at any time if another problem regarding this issue might occur.
Ciao,
Holger
by the way your criteria range doesn't have to include all the column headers from the data table but it must have headers and they must each match one of the table headers, unless you are using an advanced formula criteria cell in which case the header should not match any of the table headers-it can even be blank
Josie
if at first you don't succeed try doing it the way your wife told you to
Ok, won't close the thread just yet... couple more questions/issues...
1. is it possible to filter based on a character string in a column? (e.g. if I ONLY have BA in the cell, it'll filter and find those rows...but if I have a string of codes, like MB,BA,CR,etc), it doesn't find it. I guess it's not parsing the cell content but can it?
2. based on what JosephP just said, I was already only pulling the first 4 columns even though there is more columns, but now I want to use the 6th column, while ignoring the 5th column (and not have the 5th column copied and displayed on the main sheet). Is it possible to skip the 5th column? (so only '5' columns wile appear in results (columns 1,2,3,4,6) with their respective headers.
Thanks guys!
Wyatt...
Hi, Wyatt,
I must have missed your post, sorry for that.
Regarding the first question: maybe enter a formula like
Formula:
Please Login or Register to view this content.
Regarding the second question: I get an invalid data range if I try to split the data range. Maybe hide that column after the update is made?
Ciao,
Holger
you don't need to split the data range-use the entire table as the range to filter and place only the column headers you want copied in the first row of the location to which you are copying
Hi, Joseph,
thanks for that information (seems I never cared that much for the Advanced Flter as I should have done).
Ciao,
Holger
hi Holger
you're welcome :-) advanced filters are pretty cool!
Hey JosephP, I'm not sure what you mean... wouldn't that give an error? Like having 7 columns to look at then only display 4 or 5 so its a different number? Can you give sample code of how you mean? (i pasted my workbook in an earlier post if you want to look at it).
Thanks,
Wyatt..
I think my confusion is because the data being copied over includes the headers already so if i already have the headers i want, it'll show duplicate headers in the columns?
there's no code change required-you just need to make sure that the headers in the output destination match whichever columns you want returned
edit: you posted more while I was replying. if the destination range is blank then the headers-and all the columns-will be copied over; if it already has headers in then only the matching columns will be copied
Holger, I tried this:
but it gives a type mismatch?Please Login or Register to view this content.
you have to put the asterisks in the criteria cells not in the code ;-)
Bingo! thanks
Hi, Wyatt,
the criteria being another column not included in the result wasnīt a problem when I tried it.
Ciao,
Holger
I'm not sure if i'm doing it right, but I have to basically setup the display where the dropdown menu is in a way that mirrors the header/row...
so J1:M2 because it's the 4th row of the table and i put the header name in M1, and H4:M5 because it's the 6th row of the table and i put the header name in M4 to get it to work.
But that forces me to place the dropdown selection in a specific location to make it work? is there a better way to do that so i'm not limited in where things are put?
Holger, what I don't understand is that my 'criteria range' is basically something like "H4:M5" - this isn't what's on the sheet I'm pulling from but the area on the page where the data gets copied TO...and it's basically saying 6 columns (h to m) and it's row 4 (at M4) is the header name and row 5 (at m5) is the criteria cell itself. Like i said before, i have to do this format to tell it the number of rows and the location of the header name and criteria cell... so I can change it to "I1:N2" which would be the same as "H4:M5" where the header name is in N1 and the criteria selection list is in N2. and it's just displaying all 6 columns with the copytorange of H7:M7 and I7:N7...<--- how would I tell it to copy/display only 3 columns (that aren't continuous, like I, K, N)?Originally Posted by HaHoBe
I'm not clear what you have now but all you need to remember is
1 the criteria range only needs columns for the column(s) you want to filter and the headers of those columns must match the relevant columns in the table
2 the destination range should contain only the column headers for the columns you want copied across
ok, great! i have a better understanding of the header/criteria part!
REGARDING 2: the destination range should only contain column headers i want copied across...here's the code:
wksEMPLIST.Range("A1").Resize(FinalRow, 6).AdvancedFilter _
CopyToRange:=wksMaster.Range("I7:N7")
resize to 6 means it has to be 6 columns...the only reason I chose that was because the criteria i needed to search was in the 6th column...and then that means i was forced to copy all 6 columns over (cause specifying less in the copytorange reference gives 400 error.
is there a way to copy over non-consecutive columns...and, second part, have a criteria that searches a column but doesn't actually copy over THAT column at all (its just used to search against)?
Thanks!
Wyatt...
SOLVED, NEVERMIND THIS
i just realized...i have the filter working for two different criteria (two different buttons, filtering on two different columns), both macros work good, is there an easy way to combine them to filter for both criteria? (they just select both of the dropdown list items, and there's a third macro button saying 'FIND BOTH' (not one or the other but must have both criteria met)?
Wyatt...
Last edited by wyattea; 09-12-2013 at 09:51 PM.
put the criteria cells next to each other and use all 4 as the criteria range when you want both applied
i figured out how to do multiple criteria (that's the part i 'solved'), any idea about what I asked above (non-consecutive columns and not copying over a column that was part of the criteria?
As Josie said, just put the headers for the columns that you want copied in the first row of the range you copy to. You don't need to do anything else.
Remember what the dormouse said
Feed your head
bingo! works like a charm
thanks Josie, Holger and Rory!
Wyatt...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks