Advanced Filter Dynamic Rows Of Criteria

Mar 27, 2007

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:= _....................

View 2 Replies


ADVERTISEMENT

Dynamic Advanced Filter

Jun 21, 2006

I am trying to create a function using vba and the advanced filter function.

The spreadsheet I am using changes from week to week so it can have different amounts of columns and also rows.

This is my code.

Sub FilterCGML()

Dim rngToFilter As Range
Dim rngFilterCriteria As Range
Dim LastRow As Long, LastCol As Integer

With Sheets("Main FTE")

Range("b2000").Value = "Cost Code"
Range("b2001").Value = "5*"

It works ok if I have values in columns A:X but if for example I only have information in A:S I get a runtime error.

I know how to find the last column and last row by using


LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column

But I don't know how to modify my code to use these.

View 4 Replies View Related

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 :

[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...

View 4 Replies View Related

VBA Advanced Filter Dynamic Ranges

Jul 24, 2014

The range element below needs to be dynamic. A1 and down meaning if i was to do this out of VBA I would hold CTRL+SHIFT+DOWN but i cant do that from macro record as it doesn't work, I'm guessing this Range("A1:A73") is the element I need to change but I don't know what to?

Range("Main[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Input").Range("A1:A73"), Unique:=True

View 3 Replies View Related

Advanced Filter Using A Dynamic Date

Dec 2, 2008

I need to filter data which is 3 months from todays date or older. I'm going to use it in a macro which downloads the information from our accounts system.

I have set up the filter and it works fine when I manually put the Date in, but when I use the date 'formula' for todays date -3 months and reference the cell its in, it does not work. Here's what I have so far :

The filter from the macro :
Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("J1:J2"), Unique:=False

The way I work out the date 3 months from now (in cell K2) :
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

Cell J1 has the relevent Column Header
Cell J2 has the following formula :
=" < K2 " (there are no spaces I had to put them in so the post appeared correctly!)

View 9 Replies View Related

Dynamic Range For Advanced Filter

Jul 28, 2006

I am writing a VBA code to make an advanced filter. Instead of a fixed criteriarange, I would like it to be dynamic. As each time the number of criteria is different.

e.g.
Range("A1:G30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("L1:L18"), Unique:=False

I want the criteriarange to change. e.g. It include all the data if I put in 20 data in col L.

View 2 Replies View Related

Advanced Filter With Multiple Criteria?

Mar 5, 2014

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:

[Code] ......

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?

View 6 Replies View Related

Advanced Filter List Criteria

Oct 25, 2011

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?

View 2 Replies View Related

Multiple Advanced Filter - 3 Criteria

Feb 7, 2012

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.

View 2 Replies View Related

Advanced Filter With Double (or / Or) Criteria

Jan 11, 2013

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

View 5 Replies View Related

VBA Advanced Filter Criteria Range

Jun 12, 2008

When doing advanced filter in VBA, is there a way to set the criteriarange, without having actual cells on a worksheet with the criteria in?

I've tried criteriarange:=Array("Currency", ws.Name), but it didn't like it.

View 9 Replies View Related

Advanced Filter With Not Equal To Criteria

Nov 19, 2007

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.

View 3 Replies View Related

Set Criteria And Run Advanced Filter Not Working - Only Heading Appear

Jun 16, 2014

I think to run one report. First sheet put options and copy to data page as criteria, and run advanced filter, it only shows heading.

VB:
Sheets("Report").Select
Range("23:350").Delete
Sheets("Details").Select
Range("w8:ae9").Copy
Range("w12").Select

[Code] ......

View 2 Replies View Related

Advanced Filter With Function As Criteria Range

Feb 27, 2008

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 "".

View 14 Replies View Related

Advanced Filter, Criteria Blank Cell

Jan 7, 2010

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.

View 2 Replies View Related

Advanced Filter For Data Not In Criteria Range

Aug 12, 2006

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

View 8 Replies View Related

Adding Advanced Filter Criteria Without Altering Pivot

Feb 8, 2012

At work, I have a workbook with multiple tabs that contain lists. Each tab has a corresponding Pivot Table.

There is a business requirement that a user can specify a name, which filters the data. For the sake of simplicity use this for an example

Column Headers: First_Name ; Last_Name; Age
Row 1: Johnny; Bravo; 29
Row 2: Shane; Falco; 34
Row 3; Bobby; Shane; 15

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:

First_Name ; Last_Name; Age
=D1; ;
;=D1;
Johnny; Bravo; 29
Shane; Falco; 34
Bobby; Shane; 15

With Criteria Range = "A1:B3"

This is problematic because my Pivot tables now include 5 rows of data.

View 3 Replies View Related

Advanced Filter To Extract Records That Meet The Criteria

Nov 2, 2008

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....

View 9 Replies View Related

Forcing Advanced Filter To Cancel Criteria If Found No Matches.

Nov 25, 2009

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.

View 3 Replies View Related

Advanced Filter: Exclude Blank/Empty Cells Criteria

Sep 11, 2006

Im using an advanced filter that uses the following criteria
Days Late Note(s) Note(s)
>90 <>*agreement* <>*QTR*

This shows all data over 90 that do not incl the words agreement or QTR in a column marked Note(s).

My problem is that I do not want to show records that are Null in the Note(s) column.

Note(s)
<> Does not work (possibly because it is text and not Numbers)

as this does work if used on records that contain numbers.

View 9 Replies View Related

VBA - Advanced Filter / Find Row Items To Remove Based On Multiple Criteria

Jun 28, 2014

I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.

BillT Doc.ItemQty
F11231012.00
F2123205.00
S1123105.00
RE321202.00
F2321108.00
F2321201.00
RE321203.00
RE999808.00
F27771001.00
RE7771001.00

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.

View 8 Replies View Related

Advanced Filter To Return Rows W/blank Column

Mar 25, 2009

I have a worksheet which shows multiple tests and whether they are completed or not[code]Test 1 Person1 Test 2 Person2 Test 3...Person12. I want to search for a specific test, e.g. pH which could be in any of the "Test" columns but only return it if it is not completed. The "Person" columns will be blank if no one completed the test.

I set up the Advanced Filter criteria with all the appropriately labeled columns and for the Person columns, I put ="" but it is returning all rows which have pH in them whether they are completed or not. What should I be putting in the Person columns of the criteria to only return those rows which have the pH test but it is not completed. I attached an example of the criteria range and the filter window to help clarify (the full spreadsheet is huge)

View 3 Replies View Related

Advanced Filter Table Top Cells (rows) Across Range Of Categories?

Dec 6, 2013

I would like to only view (or otherwise mark) the highest value cells (in column g) for each category (column c),

View 3 Replies View Related

Dynamic Columns - Filter Based On Criteria?

Feb 19, 2013

I have a 6000+ row database with 5 columns across. The 5 columns have the following headings: Hair Color, Eye Color, Age, Location, and Salary.

I have created macros that will filter each column based on criteria - for example:

[Code].....

As you can see, at cell J10 and K10, I list the filter that is used for a particular search. Some searches, will only have 2 search criteria, some may have all 5, etc.

On a separate worksheet (in the workbook), I have a "Report Tab", my question (after a long build up) is how do I dynamically change the columns and values on the "Report" tab depending on the search that I perform? For example, if I did a two variable filter (Hair and Salary), those would be the only two columns on the report - if I did all five variables for the search, all columns would be on the report, and so on.

View 1 Replies View Related

Advance Filter With Dynamic Criteria & Range

Dec 4, 2008

I bring in data with these same columns A thru J. I do not know how many rows will be comming in. I have create a macro that uses the advanced filter. I have a criteria range set up on sheet 2 with 6 variables, the main data is on sheet 1. This works fine if I use all 6 variables. I wish I could select from a list box which of the 6 or all variables I need(I might select 2,3 or more variables), & have that information populate the criteria range. I only filter in place(no copying), there is only filtering on column B, the label for the criteria range will always be "Name" it will be located on sheet 2 D:1. Is this possible or should I be trying some other function? Here is the macro that uses all 6 criteria. I have searched and could not find what I was looking for.

'add criteria range
Sheets("Sheet2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("D2").Select
ActiveCell.FormulaR1C1 = "e11*"
Range("D3").Select
ActiveCell.FormulaR1C1 = "e12*"
Range("D4").Select.........

View 9 Replies View Related

Filter>Advanced Filter>Unique Failure

Jun 14, 2009

Can anyone explain why the unique filter does not produce a unique result - sample attached?

View 2 Replies View Related

Advanced Filter To Filter A List Of Data

Jan 14, 2010

How do I go about using an advanced filter to filter a list of data e.g.

boat
boat
boat
car
car
truck

and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with

boat
boat
boat

View 9 Replies View Related

Advanced Filter :: Filter Data Between Two Dates

Mar 15, 2007

1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).

2- Make the first day of a month in a color cell

I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work

3-Make Advanced Filter to filter data between two dates .

View 5 Replies View Related

Filter Rows Based On Multiple Criteria

Mar 19, 2007

Example attached. I need to filter rows based on a start date and stop date, columns C and D. So for example the filter date is 01Mar07 (located in A5). As this date in this cell is changed the rows are filtered accordingly. I need to filter rows so that any row with a start date which includes Mar 07 is shown and I need to include all rows that have an end date in Mar 07. This would result in the inclusion of an event that started in Feb and Ends in march being displayed.
Additionally, I would need to clear the filter. I'm just starting out, I'm sure this is easy for you all the excel experts., and you may probably have a better method to approach this.

View 2 Replies View Related

Filter Rows By More Than Two OR Criteria And Extracting Results Into New Sheet?

May 21, 2014

I need to filter a large database using multiple OR criterias.

Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.

I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.

Sheet1
CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
GEORGE--------------SUGAR-----------------SALT
MICHAEL-------------PEPPER-----------------CHILI
JACK-----------------BREAD------------------SALT-----------------COFFEE

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved