Get Columns

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
 
C

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top