Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I've recorded an Excel macro to AutoFilter a selection and set criteria. This will work fine for this set of data but the number of rows will vary from month to month.

    How can I modify this macro so that the range will automatically recognize changes in row length? (ActiveSheet.Range("$B$4:$T$3343").
    This code follows a macro which has deleted the prior month's rows and reset the UsedRange.


    Application.Goto Reference:="R4C2"
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$4:$T$3343").AutoFilter Field:=1, Criteria1:="<>"

    ActiveSheet.Range("$B$4:$T$3343").AutoFilter Field:=13, Criteria1:=">=0.08" _
    , Operator:=xlOr, Criteria2:="<=-0.08"

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Just Change so the Autofilter applies to ONE Cell.
    Excel should then apply it to the Current Region,
    which is what it does when you run it manually


    Code:
    ActiveSheet.Range("$B$4").AutoFilter Field:=1, Criteria1:="<>"
    
    ActiveSheet.Range("$B$4").AutoFilter Field:=13, Criteria1:=">=0.08" _
    , Operator:=xlOr, Criteria2:="<=-0.08

    However IF there any Completely Blank Rows Between Column A and T,
    before the END of the data to filter then Filtering will stop there.

    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Perfect. All of the Filtered cells contain formulas so I don't need to worry about blanks.

    Thanks for your help.

  4. #4
    New Lounger
    Join Date
    Sep 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Talking Thank You!

    Quote Originally Posted by AndrewKKWalker View Post
    Just Change so the Autofilter applies to ONE Cell.
    Excel should then apply it to the Current Region,
    which is what it does when you run it manually


    Code:
    ActiveSheet.Range("$B$4").AutoFilter Field:=1, Criteria1:="<>"
    
    ActiveSheet.Range("$B$4").AutoFilter Field:=13, Criteria1:=">=0.08" _
    , Operator:=xlOr, Criteria2:="<=-0.08
    However IF there any Completely Blank Rows Between Column A and T,
    before the END of the data to filter then Filtering will stop there.
    EXACTLY what I was looking for, the VBA equivalent of CurrentRegion to use in a filter. You are a brain surgeon, thank you again!

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi millerprm

    ..the vba equivalent of CurrentRegion is CurrentRegion, as in..

    [a1].CurrentRegion.AutoFilter

    zeddy

Posting Permissions

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