Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Advanced filter

  Asked By: Alex    Date: Mar 14    Category: MS Office    Views: 2838
  

I have a sheet called database where I filter the data in using
advanced filter.
I have a sheet called criteria that has the criteria range and the
extraction range.
1. I used codes to find my used range in the database = filterRange
2. I define my criteria range =CriteriaRange
3. I define my extraction range = ExtractionRange
4 I apply the Advanced filter codes to the ranges.

Please could look over the codes, it is not working.

Sub DailyCowList()

' Show the database used range

Worksheets("database").Activate

DmyLastCell = LastCell(Worksheets("Database")).Address
DmyRange = "a4:" & DmyLastCell
Application.ScreenUpdating = True
Range(DmyRange).Select

'to verify the range
MsgBox DmyRange
MsgBox DmyLastCell
LimitRange = "A25:" & DmyLastCell

Set FilterRange = Worksheets("Database").Range(DmyRange)

Worksheets("Criteria").Activate
With Worksheets("Criteria")
Set CriteriaRange = .Range("A2:U22")
Set ExtractionRange = .Range(LimitRange)

End With

' Advanced filter

FilterRange.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=CriteriaRange, _
CopyToRange:=ExtractionRange

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Ricardo Smith     Answered On: Mar 14

I believe your criteria  range has to be at least two rows: the top row with the
same column headings (field names) that your data  has, and the next row (or
rows), where you enter the actual filter  criteria. There must be at least one
blank row between your criteria range  and the data. If your criteria contains a
formula, leave the top criteria row (the heading) blank.

In your case, perhaps your criteria range should refer to A1:U22

 
Answer #2    Answered By: Nora Martin     Answered On: Mar 14

Thank you for your suggestion, but the top row with the same column headings
(field names) that the data  start at A2. If I do this advanced  filter with a
macro it works and the codes  is the following
Sheets("Criteria").Select
Sheets("database").Range("A3:U218").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A2:U22"), CopyToRange:=Range("A25:U300"), Unique:=
_
False
the problem lays when I assign a named range. But I cannot see the error. May be
somebody will detect it

 
Didn't find what you were looking for? Find more on VBA Advanced filter Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post