PDA

View Full Version : last row variable in auto filter



JZB
03-17-2010, 06:57 AM
Hi all

i am trying to incorporate a lastrow variable into an autofilter.

The following is not working :dunno

LASTROW = Range("A65536").End(xlUp).Row
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$" & lastrow & ").AutoFilter Field:=5, Criteria1:="0.16"

any ideas?

i am currently working with lots of data and so loops are very slow.

much appreciated for the help

Thanks

JZB

mbarron
03-17-2010, 07:43 AM
Change:
ActiveSheet.Range("$A$1:$I$" & lastrow & ").AutoFilter Field:=5, Criteria1:="0.16"
to
ActiveSheet.Range("$A$1:$I$" & lastrow).AutoFilter Field:=5, Criteria1:="0.16"

Bob Phillips
03-17-2010, 08:11 AM
You don't say why it fails, but there is a redundant quotes in there



LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows("1:1").AutoFilter
ActiveSheet.Range("$A$1:$I$" & LastRow).AutoFilter Field:=5, Criteria1:="0.16"

JZB
03-17-2010, 03:46 PM
actually guys there is another problem which i was hoping you could help with.... post filter a specific cell is selected.

ActiveSheet.Range("$A$1:$I$" & LastRow).AutoFilter Field:=5, Criteria1:="JZB"
Range("E5").select

representing the firstrow of the selected criteria.

is there a way i can replace this cell with some sort of variable as the data varies each time?

the only idea i have is to loop through the data first to identify the row the filter will point to but was hoping there was a more efficient way of doing it.

thanks for the help

JZB