Consulting

Results 1 to 4 of 4

Thread: last row variable in auto filter

  1. #1
    VBAX Regular
    Joined
    Mar 2009
    Posts
    48
    Location

    last row variable in auto filter

    Hi all

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

    The following is not working

    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

  2. #2
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    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"

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You don't say why it fails, but there is a redundant quotes in there

    [vba]

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Rows("1:1").AutoFilter
    ActiveSheet.Range("$A$1:$I$" & LastRow).AutoFilter Field:=5, Criteria1:="0.16"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    Mar 2009
    Posts
    48
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •