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]

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.

try:

Function DetectFilter() 'get filtered columns index
    Dim i As Long, j As Long, k As Long
    Dim flt
    Dim icoll As New Collection
    If Not ActiveSheet.FilterMode Then
        DetectFilter = "not filtered"
        Exit Function
    End If
    For i = 1 To ActiveSheet.AutoFilter.Filters.Count
        If ActiveSheet.AutoFilter.Filters(i).On Then icoll.Add i
    Next i
    If icoll.Count > 0 Then
        ReDim flt(1 To icoll.Count, 1 To 1)
        For j = 1 To icoll.Count
            flt(j, 1) = icoll(j)
        Next j
        DetectFilter = flt
    Else
        DetectFilter = "Nothing filtered"
    End If
End Function

in the cell in want to get result:

=IF(ISTEXT(DetectFilter()),"Not filtered",INDEX("your filter range",1,DetectFilter()))

 

[Michael Fowler]

try:

Function DetectFilter() 'get filtered columns index
    Dim i As Long, j As Long, k As Long
    Dim flt
    Dim icoll As New Collection
    If Not ActiveSheet.FilterMode Then
        DetectFilter = "not filtered"
        Exit Function
    End If
    For i = 1 To ActiveSheet.AutoFilter.Filters.Count
        If ActiveSheet.AutoFilter.Filters(i).On Then icoll.Add i
    Next i
    If icoll.Count > 0 Then
        ReDim flt(1 To icoll.Count, 1 To 1)
        For j = 1 To icoll.Count
            flt(j, 1) = icoll(j)
        Next j
        DetectFilter = flt
    Else
        DetectFilter = "Nothing filtered"
    End If
End Function

in the cell in want to get result:

=IF(ISTEXT(DetectFilter()),"Not filtered",INDEX("your filter range",1,DetectFilter()))

Thanks, but I need clarification ... Normally I trigger VBA code via a Macro, and after inserting your code above into the View Code via right-clicking the sheet tab, it doesn't appear in the list of Macros, so how do I trigger the Code ?

 

[myall_blues]

It's a user-defined function, so you enter it as if it were an Excel function (like =SUM, =VLOOKUP etc) into a cell in your spreadsheet (=DetectFilter()).

@eiloken has provided you with the way to use it in a formula to get the result you're after.

in the cell in want to get result:

=IF(ISTEXT(DetectFilter()),"Not filtered",INDEX("your filter range",1,DetectFilter()))

after inserting your code above into the View Code via right-clicking the sheet tab

Note that the code needs to be inserted as a new module, not as event code in a worksheet.

Regards

Murray

 

[Michael Fowler]

It's a user-defined function, so you enter it as if it were an Excel function (like =SUM, =VLOOKUP etc) into a cell in your spreadsheet (=DetectFilter()).

@eiloken has provided you with the way to use it in a formula to get the result you're after.




Note that the code needs to be inserted as a new module, not as event code in a worksheet.
View attachment 110409

Regards

Murray

Hi Murray. I've inserted the VBA Code as a new module, and I also have the Excel formula in a cell. They're sitting there, but how do I get it to run/activate/do its work ?

 

[myall_blues]

Where the formula says "your filter range" you should enter the data filter range without the quotes, just like a normal excel range. It will just work.

For example:

Filter on column B

Book1
	A	B	C	D
1
2	B
3
4	A	B	C	D
8	2	6	10	14
9	2	6	10	14
10	2	6	10	14
17
Sheet1

Cell Formulas
Range	Formula
A2	A2	=IF(ISTEXT(DetectFilter()),"Not filtered",INDEX(A4:D16,1,DetectFilter()))
B8:D10	B8	=A8+4

 

[eiloken]

Hi Murray. I've inserted the VBA Code as a new module, and I also have the Excel formula in a cell. They're sitting there, but how do I get it to run/activate/do its work ?

like myall_blues explain, just make sure enable macro and it will work like normal formula (just change "your filter range" in INDEX formula to the range that you filtered

 

[Michael Fowler]

like myall_blues explain, just make sure enable macro and it will work like normal formula (just change "your filter range" in INDEX formula to the range that you filtered

Okay, I have made progress, but if I have more than one column filtered the formula result is only showing one of them, whereas I need it to show all filtered columns.

 

[eiloken]

Okay, I have made progress, but if I have more than one column filtered the formula result is only showing one of them, whereas I need it to show all filtered columns.

if worked for me, be sure you place formula at range that not in filter range and not has any data, formula will return an array if you have more than one column filtered:

 

[Michael Fowler]

if worked for me, be sure you place formula at range that not in filter range and not has any data, formula will return an array if you have more than one column filtered:

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 ?