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.

ADVERTISEMENT

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

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

Advanced Filter On Name Selection

Apr 8, 2014

I have a large Excel with details of 1,000 staff

Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc

I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g

Frank, James
Wilkonson, Paul,
etc

In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR

Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?

View 11 Replies View Related

Using INDIRECT In Advanced Filter

Aug 5, 2014

I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?

View 8 Replies View Related

Advanced Filter Using Calculaions

Jul 25, 2006

I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.

View 9 Replies View Related

Automate Advanced Filter

Sep 8, 2009

Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.

I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)

Granted I copied the code whilst researching but I'm just not quite there.

Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?

View 9 Replies View Related

Advanced Filter Not Filtering In UDF?

Dec 17, 2012

why the Advanced Filter works in the macro but not in the UDF?

Code:
Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name
Dim c As Range
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

[Code]....

View 2 Replies View Related

Advanced Filter - Macro

Mar 24, 2007

Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).

Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False

Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?

View 9 Replies View Related

Advanced Filter With Wildcards

Oct 28, 2008

I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.

I have another worksheet that contains a list of parts in one column.This list is varying in row length.

I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.

When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.

View 9 Replies View Related

Advanced Filter For Text

Aug 23, 2009

I have a huge list (over 900,000 rows) of text codes (e.g. ABC-12345) on Column A Spreadsheet 1 and would like to remove the ones that are not included in Column A Spreadsheet 2.

I changed all of the text on Column A Spreadsheet 2 to have the in front of it (e.g. ABC-1234) and tried to run the advanced filter but it does nothing.

View 9 Replies View Related

Advanced Filter Cut Instead Of Copy

Feb 9, 2010

This code works, but I would like to cut (remove) the records from the original record list.

Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("H1:H3"), CopyToRange:=Range("J1:O1"), Unique:=True

View 9 Replies View Related

Automate Advanced Filter With VBA

Jan 17, 2009

I need to lookup and concatenate all the header values where the value in the corresponding row equals a certain value. For example, column D, has the column header value if the cell values equals "X". Likewise column E has the column header value if the cell values equals "Y". I have too many columns (this is just a simplified version) to use nested if statements.


resource1resource2resource3 XResources YResources
Jeff X resource2
John X X resource2 resource3
Jim Y resource1

I'm not an excel newbie, but I keep thinking there must be a simple solution that I am missing.

View 6 Replies View Related

Disable Advanced Filter

Feb 15, 2010

i am trying to work out the coinage for wages ie. 5546.75 needs to brake down into dominations ,notes and change i live in south africa so we use the desimal system. i have tried to use floor and mod as formulas but at some points it returns an error or incorect result.

View 8 Replies View Related

Sort And Advanced Filter

Jul 13, 2006

I am trying to perform a function that searches through the first column for any active "A" account and then copies the corresponding rows information into another worksheet. I think I need to use the advanced filter function however I can't seem to figure it out.

View 9 Replies View Related

Advanced Filter Between Years

Dec 4, 2006

I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.

View 9 Replies View Related

Advanced Filter Between Dates

Jan 17, 2007

I want to create an advanced filter criteria along the lines of
=AND(col1>begindate,col1<endate)

where 'col1' is the title in the cells at the head of the appropriate columns in the data range (B1), criteria range(F1) and extract area range (I1), and 'begindate' and 'enddate' are named single cells.

Why doesnt this work?

View 8 Replies View Related

Advanced Filter Only Works Once

Mar 9, 2007

i am trying to use the autofilter command to paste 4 columns else where on the page using unique = true. this only will run once. when i try to run it again everything is blank. please see code below.

Sub copyindex01()
Columns("L:O").Select
Range("O1").Activate
Selection.Delete Shift:=xlToLeft
Range("G2").Select
Columns("G:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"M:P"), Unique:=True
Range("L1").Select
End Sub

View 4 Replies View Related

Advanced Filter Vba - Workbook Name

Apr 2, 2007

I created an advanced filter macro which analyzes the "Orig_Appmt_Data" dataset from the "Summary" worksheet and provides the resulting analysis on the "Effective Rate" worksheet. However, when I change the name of the workbook, the macro fails. I was hoping someone could provide some assistance in helping adjust the vba code to take into account the subsequent changes in workbook filename. Here is the

Sub ER_Analysis()
'
Range("B8:R109").Select
Selection.ClearContents
Columns("P:R").Select
Selection.EntireColumn.Hidden = False
Range("Q5").Select
Range(" Conversion 3-28-07.xls'!Orig_Appmt_Data"). _
AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Q5:Q6"), _
CopyToRange:=Range("B8:R8"), Unique:=False
Range("B8:R8").Select
Selection.AutoFilter
Range("B8:R87").Sort Key1:=Range("P8"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=1, Criteria1:="<>", Criteria2:="<> TOTAL"
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.Font.Bold = True
Range("P8").Select
End Sub

View 3 Replies View Related

Advanced Filter With AND + OR Conditions

Nov 17, 2007

I am trying to create my critera section for an Advanced Filter, but it seems like Auto Filter uses each row as a specific filter.

i.e. If I need to filter all the "2"s in column A, but both the "1"s and "2"s in column B, I need to create two different filters like this:

Column A Column B
2 1
2 2

Instead of just:

Column A Column B
2 1
2

Is there any way I can use auto-filter without having to put in every possible combination of my criteria in as different filters? Auto Merged Post;I did try creating columns next to each other, but that seems to only work as an "AND."

Per my example, I need to return everything with column A = 2, and column B = 1 or 2.

Except in my actual spreadsheet, it's more like where A = {1,2,3,4,5} and B = {0,9,8,7,6} and C = {a,b,c,d,e}

View 7 Replies View Related

Advanced Dynamic Named Ranges

Apr 28, 2008

I have a spreadsheet with a large number of graphs on and lots of data.

Most the graphs look across 20 or so columns and one or 2 rows down.

The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.

Therefore l believe using named ranges may solve this problem...

I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))

However i need to expand on this to achieve what i need to do.

1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)

2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.

3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?

View 9 Replies View Related

Advanced Filter Macro Does Not Work?

Feb 15, 2013

I've written a macro in an attempt to automate the advanced filtering in Excel.

VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....

View 5 Replies View Related

Advanced Filter To Get Rid Off The Duplicate Lines

Feb 11, 2009

I have a large spreadsheet of data, with various columns with different bank names, the amount (column H) each bank holds plus loads of other columns. Some of the banks are duplicated, so in a way I want to do an advanced filter to get rid off the duplicate lines, except that I need the amounts (column H) summed.

View 2 Replies View Related

Advanced Filter For <> Several Text Strings

Jan 8, 2010

i have a lot of data which i am going to be running an advanced filter on. One of the criteria i am trying to filter for is a list of text values that i want to make sure a row does not contain. Lets say for example I have a list of 2 text values in column "symbols" that i would like to omit in my filtered results, "abc" and "xyz".
As of now I can only set up a criteria to omit one of those.

Symbol
<>abc

this works right now.

But if i try to put <>xyz in the cell immediately under there, I thought its supposed to be filtering for symbol that does not equal abc or xyz, but instead it doesnt filter anything out. I think it may be a syntax error or something else that I am doing wrong. Can anyone point me in the right direction?

Ideally, I would like to be able to filter out a list of text values that I keep in another column. Like lets say today I wanted to omit "abc" "xyz" and "lmnop" and I would have the advanced filter criteria just reference that list of values as what I would like to filter out. Except there would probably be a list of at least 20 to 30 values, that change over time, so it would be much easier to just keep it all neat in one column that I can edit as I need to.

View 11 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

Code For Advanced Filter Function

Dec 5, 2008

I have an Excel sheet with a few thousand rows which I would like to filter by a column with the name TrackingID. This column contains values like:

12AA1
23452BA2
234AA1
345635CA2
...

I would like to filter this column by the third character from the end (which is always a letter from the range [A-Z]). In the example above, this would be:

A
B
A
C...................

View 2 Replies View Related

Advanced Filter Code Re Automation

Jun 19, 2009

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

View 2 Replies View Related

Advanced Filter On Hidden Sheets

Jul 6, 2009

I want this macro to run when I select sheet1 so I have dropped the code into sheet1. The macro runs fine as a stand alone but I get a 1004 protection error when placed into the worksheet module. Sheet "data2" is unprotected. I have tried "calling" the macro from the sheet1 module but it loops. I know it's simple but it's kicking my ***. data2 is hidden and workbook is unprotected as of now.

View 3 Replies View Related

Using Advanced Filter In Combination With Other Filters

Jul 10, 2009

I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity.

I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period.

I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.

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

Real-Time Advanced Filter?

Feb 25, 2013

I have 300 records in one table (A:I) and would like to create another table where column I is equal to a specific value. I understand that this is possible with an advanced filter, but I would like the second table to populate in real-time instead of needing to reapply the filter each time.

I have been trying to do this with conditional vlookups, but cannot get it to give me all the unique values. Everything comes back to circular references, which I still haven't been able to get to work.

Reasoning: I have created a fantasy baseball "cheat sheet", ranking the top 300 players. Column I is a dropdown menu for the team that drafts each player. As I update this throughout the draft, I would like my "team" to auto-populate in another table as I draft players and select my name in column I.

View 4 Replies View Related

Multiple (Not) Lines Of Advanced Filter

Jun 23, 2014

I have a data table consisting of entries; for a simple example,

Column header

Entry A
Entry B
Entry C
Entry D
Entry E

I want to apply an Advanced Filter to Exclude multiple items. Say I want to exclude entries A and B. In my Advanced Filter criteria range I entered:

Column header
A
B

But since Excel reads each line of Advanced Filter criteria as "or" - i.e. does not equal A OR does not equal B - nothing gets filtered. I know the proper way to use AND in Advanced Filter is with multiple columns, such as:

Column Header
Column Header

A
B

But my exclusions will be dynamic so I would prefer to have my criteria listed vertically instead of horizontally.

View 4 Replies View Related

Listbox Advanced Filter First Letters

Dec 11, 2006

aboute searching big database (10000 rows) with names of streets for one town. Each day I get list with 50 address and I need to look in database to see details aboute street in wich is address. I was thinking of user form with listbox for list of 50 addresses and then use first four letters of that address for advanced filter. I'm stuck with code for that four letter thing. When I see the desired information I need to insert it to 50 address table.

View 9 Replies View Related

ADVERTISEMENT