Get Columns

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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)
Click to expand...

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
 
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
 
Back
Top