Enter in Cell the Column Letter that is Filtered

[Michael Fowler]

Is there a way that you can automate into a cell (either by formula or macro) the Column Letter that is filtered ?

Example : Column D is filtered, so as a result I want "D" or the Column Heading Name automatically entered into a cell.

 

[eiloken]

I think I've got it right. My formula is ... =IF(ISTEXT(DetectFilter()),"Not Filtered",INDEX(B7:BF5007,1,DetectFilter()))

Looking at your screenshot, do you also have a formula in cell B2 ?

no, my formula in B1, i had 2 filtered columns A and D, when formula detect it, if will create an array and display it like in my screenshot, you will see border of range B1:B2 is blue, that mean formula in B1 and B1:B2 display as array result

 

[Michael Fowler]

Okay. I have verified what you mentioned : that my formula is out of the filter range (B7:BF5007) as I have the formula in Row 5. It works when I have one filter selected, but then when I choose a second filter, the formula result changes to the last selected filter. Any thoughts ?

 

[eiloken]

no, my formula in B1, i had 2 filtered columns A and D, when formula detect it, if will create an array and display it like in my screenshot, you will see border of range B1:B2 is blue, that mean formula in B1 and B1:B2 display as array result

Like i said, when your filter columns is more than 1, you need to place formula at an empty range that large enough to display the result, for example, if you have 5 columns filtered, you need to make sure to place formula at the cell that has more than 4 empty cell after it

 

[Michael Fowler]

Like i said, when your filter columns is more than 1, you need to place formula at an empty range that large enough to display the result, for example, if you have 5 columns filtered, you need to make sure to place formula at the cell that has more than 4 empty cell after it

Right. I just made the change as you described, by putting the formula in a cell outside the range in column BW, there are no populated cells near it. But it's still doing the same thing, showing the last selected filter only.

 

[eiloken]

Like i said, when your filter columns is more than 1, you need to place formula at an empty range that large enough to display the result, for example, if you have 5 columns filtered, you need to make sure to place formula at the cell that has more than 4 empty cell after it

ok, maybe i should change the formula like this for easier:

Function DetectFilter() As String 'get filtered columns index
    Dim rng As Range
    Dim i As Long, j As Long, k As Long
    If Not ActiveSheet.FilterMode Then
        DetectFilter = "not filtered"
        Exit Function
    End If
    Set rng = ActiveSheet.AutoFilter.Range
    For i = 1 To ActiveSheet.AutoFilter.Filters.Count
        If ActiveSheet.AutoFilter.Filters(i).On Then DetectFilter = DetectFilter & rng.Cells(1, i).Value & "; "
    Next i
End Function

this formula will display all filtered columns in one cell

 

[Michael Fowler]

ok, maybe i should change the formula like this for easier:

Function DetectFilter() As String 'get filtered columns index
    Dim rng As Range
    Dim i As Long, j As Long, k As Long
    If Not ActiveSheet.FilterMode Then
        DetectFilter = "not filtered"
        Exit Function
    End If
    Set rng = ActiveSheet.AutoFilter.Range
    For i = 1 To ActiveSheet.AutoFilter.Filters.Count
        If ActiveSheet.AutoFilter.Filters(i).On Then DetectFilter = DetectFilter & rng.Cells(1, i).Value & "; "
    Next i
End Function

this formula will display all filtered columns in one cell

Okay, thank you for that. I didn't think it worked as the formula result wasn't refreshing when I changed filters, so I added code so that the DetectFilter formula refreshed after I filtered columns, and then it worked. I appreciate your help.

 

[eiloken]

Okay, thank you for that. I didn't think it worked as the formula result wasn't refreshing when I changed filters, so I added code so that the DetectFilter formula refreshed after I filtered columns, and then it worked. I appreciate your help.

weird, it just worked for me when i change the filter column, but if it not work for you, there is a way that you can use selection change event of worksheet to do that:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cll As Range, rng As Range
    For Each cll In UsedRange
        If cll.Formula = "=DetectFilter()" Then
            If rng Is Nothing Then Set rng = cll Else Set rng = Union(rng, cll)
        End If
    Next cll
    If Not rng Is Nothing Then rng.Formula = "=DetectFilter()"
End Sub

 

[Michael Fowler]

weird, it just worked for me when i change the filter column, but if it not work for you, there is a way that you can use selection change event of worksheet to do that:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cll As Range, rng As Range
    For Each cll In UsedRange
        If cll.Formula = "=DetectFilter()" Then
            If rng Is Nothing Then Set rng = cll Else Set rng = Union(rng, cll)
        End If
    Next cll
    If Not rng Is Nothing Then rng.Formula = "=DetectFilter()"
End Sub

Curious. Still not refreshing.

 

[eiloken]

Curious. Still not refreshing.

you need to insert it to worksheet module (red) that need to refresh, after filtered columns and select to another cell of that sheet, macro will refresh formula

 

[Michael Fowler]

you need to insert it to worksheet module (red) that need to refresh, after filtered columns and select to another cell of that sheet, macro will refresh formula

Hello again. I've got your code above as a module in the sheet that needs refreshing. I've also got =DetectFilter() in a cell outside the range being filtered. But it's still not automatically refreshing when I select an auto-filter.