Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was selected in autofilter
Hi, i want to know, how to achieve what was selected in autofilter in row 5
via VBA code and than trigger a code. Thanks. Henrich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was selected in autofilter
This function returns the filter value
Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrich" wrote in message ... Hi, i want to know, how to achieve what was selected in autofilter in row 5 via VBA code and than trigger a code. Thanks. Henrich |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was selected in autofilter
Bob,
PERFECT JOB, but one more question. Why the result beginns with '=', like =Name and not just only Name? Thanks Henrich €žBob Phillips" napĂ*sal (napĂ*sala): This function returns the filter value Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrich" wrote in message ... Hi, i want to know, how to achieve what was selected in autofilter in row 5 via VBA code and than trigger a code. Thanks. Henrich |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was selected in autofilter
because you could be filtering for equals, greater than, less than etc. so
it includes the operator. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrich" wrote in message ... Bob, PERFECT JOB, but one more question. Why the result beginns with '=', like =Name and not just only Name? Thanks Henrich "Bob Phillips" napísal (napísala): This function returns the filter value Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrich" wrote in message ... Hi, i want to know, how to achieve what was selected in autofilter in row 5 via VBA code and than trigger a code. Thanks. Henrich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to reference value selected with autofilter | Excel Worksheet Functions | |||
Autofilter - =string in selected cell | Excel Programming | |||
Why does AutoFilter display rows NOT selected from drop down box? | Excel Discussion (Misc queries) | |||
Autofilter selected arrow color | Excel Worksheet Functions | |||
Find out what is selected for AutoFilter | Excel Programming |