Filter Macro that can work for any column/active cell

[ddate]

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

 

[Dave Peterson]

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

 

[ddate]

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?

 

[Dave Peterson]

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