Excel: Cell value to have criteria from filter

[hrm1220]

I have a "totals" row that currently won't show when the customer filters. I have subtotals in the other columns in the "totals" row that need to show.

	Animal  No.    Time	Arr    Hrs./Day       
	Dog	4	9.160	0.042	0.154
	Cat	4	9.160	0.004	0.015
	Bird	4	9.160	0.042	0.154
	Fish	4	9.160	0.004	0.015
	Dog	4	9.160	0.042	0.154
	Cat	4	9.160	0.004	0.015
	Bird	1	6.500	0.490	1.275
	Fish	2	9.160	0.025	0.090
	Dog	2	6.500	0.490	1.275
	Cat	2	9.160	0.025	0.090
	Bird	1	6.500	0.490	1.275
	Fish	2	9.160	0.049	0.180
	Dog	2	9.160	0.049	0.180
Totals

So if someone filters on "Dog" I would like the word "Dog" to be in the totals row. Is there some kind of function in excel that can do this?

 

[CautionMP]

Assume '[tt]Animal[/tt]' is A1 and '[tt]Totals[/tt]' is A15. In cell E15 put the formula [tt]=SUBTOTAL(9,E2:E14)[/tt].

This should ignore the '[tt]Totals[/tt]' line when an AutoFilter is applied, and the SUBTOTAL worksheet function will take filtered values into consideration when totaling cells.

Instant programmer, just add coffee.

 

[hrm1220]

Thanks CautionMP for your input.
I already have the subtotal function in E15 and I actually want the Totals line to show. So what I need is if the customer filters on Dog, A15 (totals row) will have "Dog" as the value and E15 will have the subtotal of dogs. So the part I need help with is coming up with some time of function (if there is any)in A15 to show the value of the filtered criteria

 

[FaneDuru]

For finding the value in col A:A, in the first filtered row (except the head of the table) you may use:

FiltCr = Cells(Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas(2).Row, "A")

If you run this code in an unfiltered area an error will appeare... So I made the next code considering that I have solved the problem:

Function FiltCr() As String
Application.Volatile
 On Error Resume Next
 FiltCr = Cells(Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas(2).Row, "A")
 If Err.Number > 0 Then
   Err.Clear
   FiltCr = "Unfiltered"
   Debug.Print FiltCr: Exit Function
 End If
 On Error GoTo 0
 Debug.Print FiltCr
End Function

When I call the function from an other procedure it returns the correct filter criteria. If I try to use it like function in Excel I always obtain "Unfiltered". The line of the code which determins the second area in the filtered range doesn't work. Can somebody give to that an explanation (and a solution, too)?

Fane Duru

 

[Gavona]

Simplist answer is don't include the Totals row in the range that is autofiltered. Have it above the data you are filtering maybe. Also be aware that if you highlight the range you want to filter and then put autofilter on then that will prevent excel from 'guessing' what you want to filter - so you can prevent the last row (with subtotals) being filtered.

Alternatively you can have multiple criteria if you select Custom criteria - one of which could be
contains "total" the other equals "dog"

You have posted in the VBA forum but I am not clear why? You could get the user to type their filter criteria "dog" into a cell (or use data validation to select from a drop down list). Then trap the event to run an advanced filter to display the necessary data. Advanced filter criteria can incorporate multiple ANDs and ORs (e.g. ColA = Dog or ColA = Total or Colc = Total).

Hope one of these gives you a start.

Thanks,

Gavin

 

[hrm1220]

I appreciate your input, but I can't change or move rows.
The reason I posted this in the VBA forum, is that I was trying to find some type of worksheet or workbook function through VBA, so the customer doesn't have to do any added work. I was seeing if I could create a function that would find the filtered criteria and plug that value in the cell.

Thanks again for your input

 

[FaneDuru]

This was the idea...
If you put in the cell formula "=FiltCr()" it has to return the filtering criteria. I don't know why the function doesn't work even if the next code works:

FiltCr = Cells(Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Areas(2).Row, "A")

Since the aria is filtered running this code you may obtain the filtering criteria (knowing that it is in column "A:A.

Any suggestion?

Fane Duru

 

[hrm1220]

I'm still having trouble with VB, I'm trying to get it to re-filter.

The idea is if the enduser filters on the "No" column (B) I would like to give a subtotal in each "Totals" rows for "Time", "Arr", and "Hrs/Day" columns. But once you filter you lose the total rows. I can't move these columns or rows nor the total rows. Here's the data:

Animal No. Time Arr Hrs./Day
Dog 4 9.160 0.042 0.154
Cat 4 9.160 0.004 0.015
Bird 4 9.160 0.042 0.154
Fish 4 9.160 0.004 0.015
Dog 4 9.160 0.042 0.154
Totals
Cat 4 9.160 0.004 0.015
Bird 1 6.500 0.490 1.275
Fish 2 9.160 0.025 0.090
Dog 2 6.500 0.490 1.275
Totals
Cat 2 9.160 0.025 0.090
Bird 1 6.500 0.490 1.275
Fish 2 9.160 0.049 0.180
Dog 2 9.160 0.049 0.180

I've created a function to show the filtered criteria, (which this function is in a totals row in "No" and "Animal" Columns and of course not showing in the filter). I'm having trouble with the code to re-filter based on the criteria, so the totals row can show up with the filtered crieria. Here's the VB:

Function test() As String
Dim AutoFilter As AutoFilter
Dim Filter As Filter
Set AutoFilter = ActiveSheet.AutoFilter
Application.Volatile
For Each Filter In AutoFilter.Filters
  Debug.Print Filter.On
  If Filter.On = True Then
  test = Mid(Filter.Criteria1, 2, 5)
 
  End If
    
Next Filter
If test <> "" Then
 With ActiveSheet.[Aw1].CurrentRegion
    .AutoFilter Field:=47, Criteria1:=test
  End With
End If
End Function

I appreciate your help or a better idea.

Thank you

 

[Gavona]

Autofilter cannot shows cells that meet a condition in one column (eg =4)OR a condition in another (eg =total). So I think advanced filter is the answer.

Name the top left cell in your data "database".
Copy the column headings to the top of the sheet. Name the top left one "Criteria"
Below this cell (animal) enter "Total"
Invite the user to enter thier criteria in the third row under the relevant heading.
Create this macro:

Sub Macro2()
Range("database").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("criteria").CurrentRegion, _
Unique:=False
End Sub

That solution appears to work.
Now, consider how to further automate. You could use an event to detect the user entering in the criteria range - so they do not have to initiate the macro. You could even put the criteria range on a hidden sheet, trap the input to autofilter and convert it to advanced filter.

I don't believe you can achieve your objectives using autofilter unless you ensure that there is a value in every totals row in every column that may be filtered. If you did this then you could automate the conversion of a single criteria (=dog or =4) to multiple criteria for that column. In code:

    Selection.AutoFilter Field:=1, Criteria1:="=Totals", Operator:=xlOr, _
        Criteria2:="=Cat"

Thanks,

Gavin