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

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


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

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

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

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

End With

' Advanced filter

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



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("database").Range("A3:U218").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A2:U22"), CopyToRange:=Range("A25:U300"), Unique:=
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.