Advanced Filter Code - Criteria Range More Than 1 Row Breaks Filter
May 1, 2014
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
I have a worksheet with 6000 rows (W1), and another with 2500 rows (W2). I need to check whether the values of W2 are found in the second column of W1. As in if(iserror(search(valuex,worksheet2!B2)),"",A2) ---> resulting in something like: If the value is found in the cell B2 of column B on W1, then return its reference which you find in A2, otherwise leave a blank.
I need to check all 2500 values in all 6000 rows.
I know for sure that I will have limited hits (max of 200) so I would like to create a list on W2 (the values) where I check if they are found in W1 and return only the 'hits'. I would like to filter out the blanks.
YOu can filter the blanks, I know, but you need to have a 'full' version (I thought) with all the blanks and the hits and then and only then you can filter. But is there a way how I can use the advanced filter, with a criteria range using a function. Something like: criteria range --> if(iserror(search(valuex,worksheet2!B2)),"",A2) is not equal to "".
I m Playing around with AdvancedFilters. Using the code below I can filter for data in the CriteriaRange, but I want to filter for data not in the CriteriaRange. I can't seem to find out to do this. I'm wanting to execute this sub from a button on a userform.
Sub Filter1() Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("CRng"), Unique:=False End Sub
Can I use vba advancedfilter to work with more than one criteria?
I presently have one range designated. At the top cell has the field, or column, header name being "Student", then followed by a list of 6 people, located in Sheets("Extract").Range("A1:A7"), which is then extracted from Sheets("Complete").Range("tblPrimary[#ALL]") to Sheets("Extract").Range("AA1") as in:
I want to also be able to filter out a specific month, whose field/column name just happens to be "Month", but I suspect I will need to change it to "InfoMonth" or the like to avoid the probable key word of "Month"...
The months are numerical in those fields - 1 through 12.
Can I add to the present filtering line or do I need to then create an additional filter?
I'm trying to use an advanced filter to filter a large data set using several parameters. I had this worked out before but there have been some parameters added that have thrown my filter off balance and I'm struggling getting it back.
Here's the criteria I need to filter
Column 1: 85
Column 2: 3
Column 3: BU 1 BU 2 BU 3 BU 4
Column 4: BA 1 BA 2 BA 3 BA 4 BA 5 BA 6 BA 7
Since I have an uneven list of criteria, I'm struggling to figure out the layout with the 'AND' condition. I know I can copy the 85 and 3 figures to each line but when I have 4 figures in column 3 and 7 figures in column 4, how can I create the 'AND' condition for all these criteria?
I have a list of farmers in A1:A1000 with the types of livestock they keep in col B, delimited with commas and spaces e.g:
Col A Col B Name Animals Smith Cows, Pigs, Sheep, Horses Jones Sheep, Pigs, Chickens, Geese, Alpaccas Price Cows, Sheep, Pigs, Chickens, Rabbits Williams Cows, Chickens, Horses, Alpaccas, Pigs
I need to be able to filter this list using up to 3 criteria, e.g. Filter all farmers with Cows, AND Sheep, AND Pigs. Applying this to the data above would show Smith and Price.
The user would need to enter the criteria somewhere, preferably in 3 cells, let's say D1, D2, & D3. I reckon I need to use Advanced Filter, but not sure how to do it with all the animal types to be filtered being in one column.
as u can see on picture I have some data and i want it to filter with "debet" and "credit",while debet can be zero or number x when in the same time credit can be zero or the same number x so when entering number x meaning e.g. 500 i get to rows
first debet = 0 and credit = 500
second debet = 500 and credit = 0
while running advanced filter i was getting nothing because i couldn't write what i needed
I'd like to ask if there is anyway to use an advanced criteria with a NOT EQUAL operator.
I have a list that contains about 50 different data values that I want to filter but I want to restrict the list to not contain four different data items. Clearly, autofilter did not work as I can only specify two conditions in the custom filter. I want to use advanced filter instead but this only tests for equality.
I cannot work out the syntax to run an advanced filter for all Non-Blank Columns! I have attached an example sheet, and you can see my syntax in cell D4. I am looking to bring through a list of all line that are both Status= "NOT QUOTED YET" and Project Name= NOT Blank. Unfortunatley, the Status Row will be set as NOT QUOTED YET when there is no project name enetered, hence the problem.
attached is a spreadsheet effectively replicating a criteria box Iím using to do an advanced filter on a large amount of data (5000+ lines). The criteria can be anywhere from 1 to 7 different lines. What Iím trying to achieve is code that will look at the specified criteria box, determine how many rows of criteria actually exist, and then use that code to perform the advanced filter function on the data.
Below is the code Iíve put together so far. The problem is that this code can only determine that there are 7 total rows of criteria to use, and not the exact number of rows of criteria. For instance, say I only wanted to use 2 rows of criteria, Iím hoping the macro would only use those 2 rows instead of picking up all 7 rows.
See criteria box on spreadsheet for example. In this particular example, Iím wanting the macro to only use B49:I51 as criteria. The ideal solution would be for the macro to look at the criteria box and determine the last row used that is not filled with ď1Ēs. Does anybody have any thoughts on ways to tweak my code to get it to achieve this?
Sub RunDynamicSelection_Click() Dim wsSheetDS As Worksheet Dim wsSheetRS Set wsSheetDS = Worksheets("DataSheet") Set wsSheetRS = Worksheets("ReportSelection") With wsSheetDS .AutoFilterMode = False With wsSheetDS. Range(("A4:N4"), wsSheetDS.UsedRange.Rows(Worksheets("DataSheet") _ .UsedRange.Rows.Count)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _....................
I'm trying to automate advanced filter but can't get past having just 1 critreria. I have attached an example spreadsheet where the download dump is on "Data" sheet and the result of the advanced filter is on the "Result" page. I would like, if possible, for the result to come up every time the criteria is put in (they are all data validation lists).
so for example, if i select account code, all those codes come up then select period and the list dwindles down and again for cost centre. Is it possible to do this without using macros. I have found the following code butnot sure what to do with it (sorry).
What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.
The user specifies "Shane" as the filter in another pre-determined Cell (D1). Using advanced criteria, I need to find all rows that have Shane in either First_Name or Last_Name. The only way I know how to do that is inserting two rows and adding criteria:
I am trying to use an advanced filter to extract records that meet the criteria in the blue input cells. I can't get the criteria correct to allow me to meet the 3 conditions. There are duplicate names in the list so I will need to use unique records only option....
I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.
If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.
Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.
the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?
Sub myfilt() Application. ScreenUpdating = False If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If Application.CutCopyMode = False Application.ScreenUpdating = True
I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match. The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.
So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.
I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.
I thought I had this cracked last month when I ran a set of reports using some code dependent on using advanced filters to first create a unique list to loop through and then to create a list of all entries in a list for each unique entry. The idea being that I extracted all the data for each unique entry in the list and saved that to a new workbook, named after the unique entry.
However, this month I'm totally flummoxed again. I can't seem to get even the first part, creating a unique list, to run.
I have a sheet with several thousand rows of data with maybe 25 columns. I have the following code to try to extract the unique entries in the 2nd column, column B and store them in the same worksheet in column AD, to use when looping through the list.
VB: With ActiveSheet .Range("AD1").EntireColumn.Delete .Range("A1").CurrentRegion.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range( _
At the moment the macro filters the range down to row 10,000. I need to adapt it to a variable number of rows in case they go over 10,000. Currently I am using a Do loop to find the bottom of the list by having it check the one column out of 26 that will always have data in it (Column D)
Code: Dim rfRegSelect As Range Set rfRegSelect = Range(Cells(rfRegisterStarts - 1, 1), Cells(rfRegisterStarts + rfRegisterRowCounter, 27)) rfRegSelect.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Register").Range("A3:W4"), _ CopyToRange:=Sheets("Search").Range("A26:X26"), _ Unique:=False With almost every combination of With Worksheet("Register") or Sheets("Register") I can imagine
Why I'm getting these errors? Is Advanced Filter just really finicky?
I have many sheets which I am using the advanced filter to filter the data to a single summary sheet. Everthing works great with one exception. I have cell comments added with relevent info to to cell values. When the advanced filter is performed the cell comments are not transfered to the summary sheet with the cell values. Is there a method to filter the data to another sheet and keep the comments?