PDA

View Full Version : Changing AutoFilter Range



Opv
07-16-2010, 07:28 AM
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?

Bob Phillips
07-16-2010, 08:03 AM
If i is autofiltered, why would you want to show it? That isn' filtered then.

Opv
07-16-2010, 08:16 AM
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.

Bob Phillips
07-16-2010, 08:36 AM
Doesn't re-filtering work? Not clearing the filter, just re-issuing the filter command with criteria.

Opv
07-21-2010, 10:58 AM
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?

Opv
07-21-2010, 12:10 PM
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.



'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

mdmackillop
07-21-2010, 12:49 PM
Can you post some sample data?

Opv
07-21-2010, 06:00 PM
I've about decided to fore-go the additional filter criteria that is causing the problem.