Filter Macro that can work for any column/active cell
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
ddate wrote:
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" wrote:
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 wrote:
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
--
Dave Peterson
|