Get Columns

[Steve]

Hi I came across this great macro to identify which columns in a multi
column spreadsheet are filtered (Credit to Juan Pablo Gonzalez)

Sub GetColumns()
Dim Sht As Worksheet
Dim i As Long

Set Sht = ActiveSheet

With Sht.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then
MsgBox .Range(1, i).Column
End If
Next i
End With
End Sub

However, the message box only tells you which number of column is
filtered. ie 1 2, 3, 56 etc. Is it possible to return the actual
column header name (i.e name, address etc) or even the column title
(AA, BZ, GG etc)

Thanks in advance

Steve

 

[Claus Busch]

Hi Steve,

Am Sun, 07 Oct 2012 12:52:43 +0100 schrieb Steve:

However, the message box only tells you which number of column is
filtered. ie 1 2, 3, 56 etc. Is it possible to return the actual
column header name (i.e name, address etc) or even the column title
(AA, BZ, GG etc)

for column header:
With Sht.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then
MsgBox Cells(1, i)
End If
Next i
End With

for column character:
With Sht.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then
myLen = InStr(1, Columns(i).Address(0, 0), ":") - 1
MsgBox Left(Columns(i).Address(0, 0), myLen)
End If
Next i
End With


Regards
Claus Busch

 

[Claus Busch]

Hi Steve,

another solution for columns character:
With Sht.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then
myArr = Split(Columns(i).Address(0, 0), ":")
MsgBox myArr(0)
End If
Next i
End With


Regards
Claus Busch

 

[Steve]

As ever Claus, thanks very much for your help

Cheers

Steve