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







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