Consulting

Results 1 to 8 of 8

Thread: Changing AutoFilter Range

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    Changing AutoFilter Range

    When specified rows in a worksheet are filtered using autofilter, is there any way to unfilter only one row within the filtered range or does one have to totally turn off autofilter and then refilter the entire range?

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    If i is autofiltered, why would you want to show it? That isn' filtered then.
    ____________________________________________
    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

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    Quote Originally Posted by xld
    If i is autofiltered, why would you want to show it? That isn' filtered then.
    The reason the row was initially filtered no longer applies to the row in question.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Doesn't re-filtering work? Not clearing the filter, just re-issuing the filter command with criteria.
    ____________________________________________
    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

  5. #5
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location

    AutoFilter Challenge

    Indeed, it does. Who would have thunk it.

    OK, I got excited when I successfully created a filter and it actually worked. Now I'm trying to expand the filter to include four non-contiguous columns of data across my database (28 total columns of data) and I have exceeded my level of competence.

    Here are the specifications:

    data headings = Range("A2:AB2")
    gRows = number of data rows
    gCols = number of data columns

    First Criteria: The heading named "Status" in Columns (28) should "=1" (This code means the goat is alive and on current inventory)

    Second Criteria: The heading named "Gender" in Columns(9) should = "Doe".

    Third Crieria: The heading named "Age" in Columns(10) should = ">=1" UNLESS the under-age doe under age one has already been bred, i.e., there is a date in the heading called "BredDate" in Columns(13)

    I want VisibleDropDown = False for ALL columns.

    Anyone want to take a stab at this filter?

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    The following code is "almost" what I need. the problem with the following is that it includes ALL underage does, not just those who have a date in the BredDate column. I can't figure out how to contrast criteria across columns, i.e., All Does age 1 or older that are in the inventory PLUS underage does that have been bred.

    [VBA]

    'KIDDING VIEW
    Sub goKidding()

    Call myOptimizer: clearClutter: Range("ToSale").Value = "ToSale"

    Dim i As Integer

    ActiveWorkbook.CustomViews("Kidding").Show

    ''''' FILTER TO DISPLAY ONLY DOES '''''
    With Sheets("(History)")
    .AutoFilterMode = False
    .FilterMode = False
    With .Range("I2:AB" & gRows)
    'Select Does
    .AutoFilter
    .AutoFilter field:=1, Criteria1:="Doe", visibledropdown:=False
    .Include under-age does that have been bred
    .AutoFilter field:=13, Criteria1:="<>""", visibledropdown:=False
    'Include only Does
    .AutoFilter field:=2, Criteria1:=">=1", visibledropdown:=False
    'Include only Does currently in inventory
    .AutoFilter field:=20, Criteria1:="=1", visibledropdown:=False
    For i = 3 To 19
    .AutoFilter field:=i, visibledropdown:=False
    Next i
    End With
    End With

    Call homePosition: myReset

    End Sub

    [/VBA]

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Can you post some sample data?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    VBAX Expert
    Joined
    Feb 2010
    Posts
    696
    Location
    I've about decided to fore-go the additional filter criteria that is causing the problem.

Posting Permissions

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