Prev: Cell formating in Excel?
Next: Excel coolumn headings changed from A, B, C, etc. to 1, 2, 3
From: merry_fay on 17 Mar 2010 09:30
Hiya,

I want to set an autofilter in my macro, but the autofilter field isn't
necessarily always going to be the same number. It will always have the same
column header though.

Is there a way to change the number to the column header, or make it filter
on the 'active column'?

Selection.AutoFilter Field:=14,

Thanks
merry_fay
From: Dave Peterson on 17 Mar 2010 10:07
Maybe you could just remove any existing filter and then filter the single
column by what you want.

Don't select the entire range (multiple columns), just the column that you want
to filter.

The entire row will still be hidden/shown when you filter.

Then it's gonna be something like:

Dim wks As Worksheet
Set wks = ActiveSheet
wks.AutoFilterMode = False
ActiveCell.EntireColumn.AutoFilter field:=1, Criteria1:="asdf"

merry_fay wrote:
>
> Hiya,
>
> I want to set an autofilter in my macro, but the autofilter field isn't
> necessarily always going to be the same number. It will always have the same
> column header though.
>
> Is there a way to change the number to the column header, or make it filter
> on the 'active column'?
>
> Selection.AutoFilter Field:=14,
>
> Thanks
> merry_fay

--

Dave Peterson
From: Dave Peterson on 17 Mar 2010 10:11
If you want those arrows on all your headers, you could use something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngToFilter As Range
Dim myField As Long

Set wks = ActiveSheet

With wks
.AutoFilterMode = False
Set RngToFilter = .Range("A1:O" _
& .Cells(.Rows.Count, "A").End(xlUp).Row)
myField = ActiveCell.Column

If myField > RngToFilter.Columns.Count Then
MsgBox "Select a cell in the right range!"
Exit Sub
End If
RngToFilter.AutoFilter field:=myField, Criteria1:="asdf"
End With

End Sub

This filters columns A:O based on the used rows in column A.





merry_fay wrote:
>
> Hiya,
>
> I want to set an autofilter in my macro, but the autofilter field isn't
> necessarily always going to be the same number. It will always have the same
> column header though.
>
> Is there a way to change the number to the column header, or make it filter
> on the 'active column'?
>
> Selection.AutoFilter Field:=14,
>
> Thanks
> merry_fay

--

Dave Peterson
 | 
Pages: 1
Prev: Cell formating in Excel?
Next: Excel coolumn headings changed from A, B, C, etc. to 1, 2, 3