Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VBA Advanced Filter Criteria Range

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Advanced Filter With Function As Criteria Range
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 Replies!   View Related
Advanced Filter For Data Not In Criteria Range
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 Replies!   View Related
Advanced Filter With Not Equal To Criteria
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 Replies!   View Related
Advanced Filter, Criteria Blank Cell
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 Replies!   View Related
Advanced Filter Dynamic Rows Of Criteria
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 Replies!   View Related
Advanced Filter To Extract Records That Meet The 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....

View Replies!   View Related
Advanced Filter: Exclude Blank/Empty Cells Criteria
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.

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

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

View Replies!   View Related
Forcing Advanced Filter To Cancel Criteria If Found No Matches.
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 Replies!   View Related
Advanced Filter Vba - Workbook Name
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()
Selection.EntireColumn.Hidden = False
Range(" Conversion 3-28-07.xls'!Orig_Appmt_Data"). _
AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Q5:Q6"), _
CopyToRange:=Range("B8:R8"), Unique:=False
Range("B8:R87").Sort Key1:=Range("P8"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Selection.AutoFilter Field:=1, Criteria1:="<>", Criteria2:="<> TOTAL"
Selection.EntireColumn.Hidden = True
Selection.Font.Bold = True
End Sub

View Replies!   View Related
Automate Advanced Filter With VBA
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 Replies!   View Related
Get Unique Records Using Advanced Filter From VBA
i need to use the advance filter=>unique records only feature from my macro... how would i do tat? i have 3 columns... column A has records which are repeated... column B and column C's values for a corresponding column A's value are the same...

a 3 6
b 4 7
c 8 9
d 1 2
a 3 6
b 4 7

.... and so on...

i need to use tat feature so tat i can filter column A alone and then copy column A, column B and column C's value to columns E,F and G...

View Replies!   View Related
Unique List Without Vba Or Advanced Filter
I have a list of item numbers that will be repeated numerous times. I would like to extract a list of unique values that would update when a new number is added. I would really like to do this with formulas. Both of the list will be on the same sheet. I have racked my brain trying to figure out a way to do this. I thought I could use LOOKUP and MAX for the first cell, and then subtract one from the number above for the following LOOKUP formulas. That did not work. Probably because my list is not sorted.

View Replies!   View Related
Dynamic Range For Advanced Filter
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.

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 Replies!   View Related
Returning Advanced Filter Results Into Row Not Range
I am trying to create an advanced filter that filters my results into a row.



would return

Shane Tom Paul Mike

I have tried changing this piece of code

Range("M1:M300").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns("M:M"), CopyToRange:=Rows("25:25"), Unique:=True

The problem is with


I have tried using Range instead of Rows but I am not having any joy.

View Replies!   View Related
Advanced Filter To Filter A List Of Data
How do I go about using an advanced filter to filter a list of data e.g.


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


View Replies!   View Related
Filter>Advanced Filter>Unique Failure
Can anyone explain why the unique filter does not produce a unique result - sample attached?

View Replies!   View Related
Advanced Filter :: Filter Data Between Two Dates
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 Replies!   View Related
Filter On Multiple Criteria Using VBA
I have a dataset, which I would like to sort on multiple criteria. The code I have is :

Dim Lst As Long
Dim Hdr As Range

Lst = Range("A65536").End(xlUp).Row
Set Hdr = Range("A6:AD" & Lst)

With Hdr
.AutoFilter Field:=9, Criteria1:="Complete" 'Match Status
.AutoFilter Field:=14, Criteria1:=Array ("Pending", "Technical", "PR", "Regional", "=") 'Case Study Status
End With

Field 9 Match Status will always be Criteria "Complete"
Field 14 may contain different variables depending on the dataset. I want the filter to bring back all Match Statuses (Field 9) "Complete" and multiple Case Study Statuses in Field 14.

This code filters correctly on Field 9, but is only bringing back blank cells in Field 9 when there is data that has Case Study Status "Pending", "Regional" and "Blank".

View Replies!   View Related
Filter By Criteria To Another Range
i want to display the results of one criteria using VBA codes instead of just filtering one by one to display the result and collate it. I have attached the sample. I want to find the "subcodes" of name "kathy". to be able to do that, i filter "kathy" to display all the "codes" then filter all the "codes" to find the "subcodes". is there vba code for this one so that when i input the name, all the subcodes will be displayed?

View Replies!   View Related
Automate Advanced Filter
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 Replies!   View Related
Advanced Filter For Text
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 Replies!   View Related
Advanced Filter With Wildcards
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 Replies!   View Related
Advanced Filter - Macro
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("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 Replies!   View Related
Sort And Advanced Filter
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 Replies!   View Related
Advanced Filter Between Years
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 Replies!   View Related
Advanced Filter Using Calculaions
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 Replies!   View Related
Advanced Filter Between Dates
I want to create an advanced filter criteria along the lines of

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 Replies!   View Related
Advanced Filter Only Works Once
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()
Selection.Delete Shift:=xlToLeft
Columns("G:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"M:P"), Unique:=True
End Sub

View Replies!   View Related
Advanced Filter With AND + OR Conditions
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

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 Replies!   View Related
Dynamic Advanced Filter
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 Replies!   View Related
Disable Advanced Filter
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 Replies!   View Related
Advance Filter With Dynamic Criteria & Range
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
ActiveCell.FormulaR1C1 = "Name"
ActiveCell.FormulaR1C1 = "e11*"
ActiveCell.FormulaR1C1 = "e12*"

View Replies!   View Related
Advanced Filter To Filter Cell Comments With The Cell Values
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?

View Replies!   View Related
Advanced Filter Help To Remove Header
Sub filtertest()
Range("C17:L33").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("C13:C14"), CopyToRange:=Sheets("Combined Summary").Range("B7")
End Sub

This works fine - it filters the data in C17:L33 on the source sheet and then copies it to the combined summary (beginning in cell B7) but when it copies the data it also copies the header row - i only want to the data to be copied? - how can i amend the above??

Also - there will be a few tables i'll be amending the above code to work on as i'll be pulling them into 1 summary - how can i amend the B7 section so that they paste into the next free row - because at present they will just past over one another?.........

View Replies!   View Related
Advanced Filter For <> Several Text Strings
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.


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 Replies!   View Related
Error Handling Of Advanced Filter
If there are records meeting the criteria are found, they are to be copied to a 2nd worksheet. Otherwise, the remainder of the code is to be executed.
I am getting an error "No Cells were found" (which is true since the autofilter doesn't net any results) with the line highted in red.

Consider this snippit of
' courts
Dim lcrtcopy As Long
End_RowDest = 0
With Worksheets("Crts_Temp")
If .FilterMode Then .ShowAllData 'disable advanced filter of data if applicable
End With..........

View Replies!   View Related
Advanced Filter Using A Dynamic Date
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) :

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 Replies!   View Related
Advanced Filter Not Working In 2007
I am trying to migrate a model that I built in Excel 2002 to Excel 2007. The model has an advanced filter in it to get unique values. However in 2002 the filter worked correctly but in 2007 it produces nothing. The code excerpt is below :-

Worksheets("Transaction Data").Range("PCT_List").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Transaction Data").Range("criteria"), _
CopyToRange:=Worksheets("Transaction Data").Range("Unique_PCT_List"), Unique:=True

View Replies!   View Related
Advanced Filter Cut Instead Of Copy
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 Replies!   View Related
Advanced Filter Copy To Another Location
I seem to be having an odd problem with Advanced Filter. When I use the Copy to Another Location option I get an error that:

"The extract range has an illegal or missing field name".

As far as I can tell, there are no blank or missing fields. I did originally have a DATE field but I have renamed this in case it was a "reserved" name but still have the problem.

When I used the filter in place it works correctly.

I am trying to use Advance Filter as opposed to lots of looping, copying and pasting and this is the only snag. I suppose that I could filter in place then copy/paste the results to the other sheet but I'd like to see where I may be going wrong.

View Replies!   View Related
Using Advanced Filter In Combination With Other Filters
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 Replies!   View Related
Code For Advanced Filter Function
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:


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:


View Replies!   View Related
Listbox Advanced Filter First Letters
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 Replies!   View Related
Advanced Filter On Hidden Sheets
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 Replies!   View Related
Advanced Filter Only Returning Headings
Take a look at the attached which shows a quick sample of a set of data and an accompanying criteria box. I'm trying to run an AdvancedFilter on it, but nothing seems to work. When I run the filter, I get zero rows returned. I've tried filtering using the headings in the criteria box and tried it without... but not much luck. I'm guessing the problem has to do with the data itself, and not the criteria box.

View Replies!   View Related
Advanced Filter Code Re Automation
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 Replies!   View Related
Macro Code Using Advanced Filter
This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).


I want it to autofilter school njit


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.

View Replies!   View Related
Advanced Filter In Shared Workbook
Can you use or apply advanced filters in a shared workbook?

I have a workbook project where I am dynamically applying advanced filters through VBA.

The intention is to share the workbook at some point for data entry among a group of people. I did not see advanced filtering listed in Excel Help as one of the features that are not available, but I did note that if I try to apply one through the menu bar it is greyed out in a shared workbook (autofilter is still available).

View Replies!   View Related
Displaying Repeated Advanced Filter Results
I created a user form that provides a menu that allows users to perform advanced filtering. They can also scroll through the results freely. A problem is that a subsequent advanced filter selection does not always display the top row of filtered records, depending upon what the user has chosen to display prior to selecting the next set of filtered records.

How can I make the top row of filtered records always be displayed so that users will not overlook results of advanced filter operations?

View Replies!   View Related
Multiple Filters ,Advanced Filter And Autofilter
using VBA I have a control sheet which summerises variouse counts & totals of data held on a detail sheet. Bu using filters and counting the visable rows.

Statistics on 50 columns of data held in several thousand (rows) mixed around eight business regionsheld in first column.

I could determin the number of affected rows by using Tick boxes on the control sheet and applying filters to the detail records

A) checkbox indicates if I need filtering on the type of data in my detail sheet and apply the filter
Selection.AutoFilter Field:=XX, Criteria1:="Y"

B) Because I could not have more than two criteria on an autofilter column I resorted to using Advanced Filter on the column with the Business UNIT's,
I Create a range write the criteria of the records to be filtered into the range, then apply an advanced filter using that range.

Both of these work well indevidually, but I am getting inconsistant results when I mix them

using the autofilter route I can select multiple tick boxes and the output is correct, and using the advanced filter I can select any combination of business units and the output is correct, however I cant get them to work together

View Replies!   View Related
Copyright © 2005-08, All rights reserved