Advanced filter - vba method 'range' of object '_worksheet' failed

[sassora]

Hi,

I would like to use an advanced filter but can't figure out why the range is causing problems, any ideas?


Dim firstrow, lastrow As Long
Dim shsortlist, converttohtml references As Worksheet
Dim r, r1 As Range
Set shsortlist = Worksheets("Subprogramme sort list")
Set r = Worksheets("Subprogramme sort list").Range("E" & firstrow - 1, "H" & lastrow)
Set references = Worksheets("References")
shsortlist.Select
r.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=references.Range("A30"), Unique:=True

[Bob Phillips]

You haven't loaded firstrow or lastrow, so you are trying to set a range E-1:H0, totally invalid

[sassora]

You haven't loaded firstrow or lastrow, so you are trying to set a range E-1:H0, totally invalid

Sorry I should have included that in the above


firstrow = 3
lastrow = shsortlist.Range("A" & shsortlist.Rows.Count).End(xlUp).Row

[Bob Phillips]

Why don't you post the actual code, all of it, avoid us picking up spurious errors?

[snb]

You als need a criterion range:


sub snb()
with Worksheets("Subprogramme sort list").cells(1,5).currentregion.resize(,4)
.row(1).copy Worksheets("Subprogramme sort list").cells(1,20)
Worksheets("Subprogramme sort list").cells(2,20)="criterion"
.AdvancedFilter xlFilterCopy, Worksheets("Subprogramme sort list").cells(1,20).currentregion,Worksheets("References").Range("A30"), True
end with
end sub

[sassora]

EDIT: the fact that there was a blank column title in the source list was causing an issue with references


I am creating a unique list using advanced filter so I don't think I need the criteria?



The full code is:


Dim firstrow, lastrow, uniquelistlastrow, uniquelistlastrow2 As Long
Dim shsortlist, converttohtml As Worksheet
Dim r, r1 As Range
Set shsortlist = Worksheets("Subprogramme sort list")
Set references = Worksheets("References")
firstrow = 3
lastrow = shsortlist.Range("A" & shsortlist.Rows.Count).End(xlUp).Row
references.Range("A31:D" & Rows.Count).ClearContents
' Unique (sorted) list
shsortlist.Select
Set r = shsortlist.Range("E" & firstrow - 1, "H" & lastrow)
r.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=references.Range("A30:D30"), Unique:=True
uniquelistlastrow = references.Range("A" & references.Rows.Count).End(xlUp).Row
Set r1 = references.Range("A30", "A" & uniquelistlastrow)
references.Select
r1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A" & uniquelistlastrow + 1), Unique:=True

[snb]

Avoid 'Select' and 'activate' in VBA (and redundant variables).


Sheets("Subprogramme sort list").cells(1,5).currentregion.offset(1).AdvancedFilter xlFilterCopy, , sheets("references").Range("A30"), True