Filter Macro that can work for any column/active cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ddate - 06 Dec 2007 23:40 GMT

I have recorded a macro but unfortunately it only works for one column. I
would like it to apply to any column and be dependent on what is the active
cell. How do i change this code to do that?

I'm not sure what the field = 24 stands for and what I can replace it with
to make it based on the active cell.

Sub Filter0()
Selection.AutoFilter Field:=24, Criteria1:="<>0", Operator:=xlAnd
End Sub

Reply to this Message

Dave Peterson - 07 Dec 2007 00:19 GMT

24 represents the 24th column/field in the range to be filtered.

If you're filtering A1:Z99, then field 24 would be column X.

If you're filtering X1:BC999, then field 24 would be AU.

This may work for you:

Option Explicit
Sub testme()
Dim myRng As Range

Set myRng = ActiveCell.CurrentRegion

If myRng.Rows.Count < 2 Then
Beep 'not enough rows
Exit Sub
End If

ActiveSheet.AutoFilterMode = False

myRng.AutoFilter _
field:=ActiveCell.Column - myRng.Column + 1, _
Criteria1:="<>"

End Sub

> I have recorded a macro but unfortunately it only works for one column. I
> would like it to apply to any column and be dependent on what is the active
[quoted text clipped - 6 lines]
> Selection.AutoFilter Field:=24, Criteria1:="<>0", Operator:=xlAnd
> End Sub

Signature

Dave Peterson

Reply to this Message

ddate - 07 Dec 2007 01:10 GMT

That's great. It works. Is there any way to code a small enhancement. When
it sets up the column filter it does so at the top of the column as that is
included in the current region. In most cases my header that I want to
filter from starts lower like in row 3 of column A and not row 1. Is there a
way to filter from the active cell & down?

> 24 represents the 24th column/field in the range to be filtered.
>
[quoted text clipped - 33 lines]
> > Selection.AutoFilter Field:=24, Criteria1:="<>0", Operator:=xlAnd
> > End Sub

Reply to this Message

Dave Peterson - 07 Dec 2007 02:27 GMT

Maybe...

Option Explicit
Sub testme()
Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(.Rows(ActiveCell.Row & ":" & .Rows.Count), _
ActiveCell.CurrentRegion)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "not in a ""good"" cell"
Exit Sub
End If

If myRng.Rows.Count < 2 Then
Beep 'not enough rows
Exit Sub
End If

ActiveSheet.AutoFilterMode = False

myRng.AutoFilter _
field:=ActiveCell.Column - myRng.Column + 1, _
Criteria1:="<>"

End Sub

> That's great. It works. Is there any way to code a small enhancement. When
> it sets up the column filter it does so at the top of the column as that is
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message