PDA

View Full Version : Solved: Advanced filter - vba method 'range' of object '_worksheet' failed



sassora
04-16-2012, 12:39 AM
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
04-16-2012, 12:46 AM
You haven't loaded firstrow or lastrow, so you are trying to set a range E-1:H0, totally invalid

sassora
04-16-2012, 12:58 AM
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
04-16-2012, 01:45 AM
Why don't you post the actual code, all of it, avoid us picking up spurious errors?

snb
04-16-2012, 09:21 AM
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
04-16-2012, 11:00 PM
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
04-17-2012, 01:17 AM
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