Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Macro that can work for any column/active cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Macro that can work for any column/active cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Macro that can work for any column/active cell
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter on active cell | Excel Programming | |||
Macro to Filter on active cell within autofilter | Excel Programming | |||
on which column is the filter active? | Excel Programming | |||
Active Cell to be used as filter... | Excel Programming | |||
how do find out the column of the active cell in a macro? | Excel Programming |