How to make Bullen's FilterCriteria() data refresh real-time?
 

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria() Function.

That said, apparently the only way to refresh the data in the display cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second best, by F9?

FYI (Background Info):

************************************************** **********II placed this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called" for
each refresh?

TIA Dennis

You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't recalculate until excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria() Function.

That said, apparently the only way to refresh the data in the display cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second best, by F9?

FYI (Background Info):

************************************************** **********II placed this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called" for
each refresh?

TIA Dennis

--

Dave Peterson

You could add
Application.Volatile
at the start.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dennis" wrote in message
...
Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the display cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second best, by
F9?

FYI (Background Info):

************************************************** **********II placed this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called"
for
each refresh?

TIA Dennis

But changing the filter criteria forces a recalc, and that is what drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't recalculate until
excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second best,
by F9?

FYI (Background Info):

************************************************** **********II placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called"
for
each refresh?

TIA Dennis

--

Dave Peterson

BTW, I would make this slight amendment so that it doesn't show blank when
no filter is applied (personal preference)

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Application.Volatile
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then
Filter = "All"
GoTo Finish
End If
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You could add
Application.Volatile
at the start.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dennis" wrote in message
...
Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second best,
by
F9?

FYI (Background Info):

************************************************** **********II placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called"
for
each refresh?

TIA Dennis

I used xl2003 and did Data|Filter|showall (slightly different than changing the
filter, though).

The old criteria still showed up. Hitting F9 made it disappear.

Bob Phillips wrote:

But changing the filter criteria forces a recalc, and that is what drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't recalculate until
excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second best,
by F9?

FYI (Background Info):

************************************************** **********II placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be "force-called"
for
each refresh?

TIA Dennis

--

Dave Peterson

--

Dave Peterson

Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it :-)

Regards

Bob

BTW I liked the TEXT technique.


"Dave Peterson" wrote in message
...
I used xl2003 and did Data|Filter|showall (slightly different than
changing the
filter, though).

The old criteria still showed up. Hitting F9 made it disappear.

Bob Phillips wrote:

But changing the filter criteria forces a recalc, and that is what
drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't recalculate
until
excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the
display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second
best,
by F9?

FYI (Background Info):

************************************************** **********II
placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be
"force-called"
for
each refresh?

TIA Dennis

--

Dave Peterson

--

Dave Peterson

Did Data|filter|Showall cause a recalc in the version you're using?

Bob Phillips wrote:

Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it :-)

Regards

Bob

BTW I liked the TEXT technique.

"Dave Peterson" wrote in message
...
I used xl2003 and did Data|Filter|showall (slightly different than
changing the
filter, though).

The old criteria still showed up. Hitting F9 made it disappear.

Bob Phillips wrote:

But changing the filter criteria forces a recalc, and that is what
drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't recalculate
until
excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the
display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second
best,
by F9?

FYI (Background Info):

************************************************** **********II
placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in the
worksheet? Is it because this particlar Function must be
"force-called"
for
each refresh?

TIA Dennis

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Lol. I assumed the Showall was a 2003 new feature. I have never used it
myself, I always click the dropdown and select (All).

You are correct though, it doesn't force a recalc. I find that a bit odd, as
the way that I do it does force a recalc?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
Did Data|filter|Showall cause a recalc in the version you're using?

Bob Phillips wrote:

Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it :-)

Regards

Bob

BTW I liked the TEXT technique.

"Dave Peterson" wrote in message
...
I used xl2003 and did Data|Filter|showall (slightly different than
changing the
filter, though).

The old criteria still showed up. Hitting F9 made it disappear.

Bob Phillips wrote:

But changing the filter criteria forces a recalc, and that is what
drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't
recalculate
until
excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's
FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the
display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second
best,
by F9?

FYI (Background Info):

************************************************** **********II
placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in
the
worksheet? Is it because this particlar Function must be
"force-called"
for
each refresh?

TIA Dennis

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Strange < surprising in excel.
<vbg

I actually added the "show all" button to a toolbar. I was tired of going to
each filter and choosing All (or clicking on data|Filter|showall) each time I
wanted to see, er, all the data.



Bob Phillips wrote:

Lol. I assumed the Showall was a 2003 new feature. I have never used it
myself, I always click the dropdown and select (All).

You are correct though, it doesn't force a recalc. I find that a bit odd, as
the way that I do it does force a recalc?

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
Did Data|filter|Showall cause a recalc in the version you're using?

Bob Phillips wrote:

Don't have 2003 Dave, so I can't play with that.

If only I had a real reason to get it :-)

Regards

Bob

BTW I liked the TEXT technique.

"Dave Peterson" wrote in message
...
I used xl2003 and did Data|Filter|showall (slightly different than
changing the
filter, though).

The old criteria still showed up. Hitting F9 made it disappear.

Bob Phillips wrote:

But changing the filter criteria forces a recalc, and that is what
drives
the function, so that should be fine.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
You could make your formula look like:

=filtercriteria(A1)&TEXT(RAND(),"")

or you could add "application.volatile" to your code.

Option Explicit
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Application.Volatile
Dim Filter As String

But the bad thing is that either way, the formula won't
recalculate
until
excel
recalculates.

Hit F9 to force a recalc before you trust the value in the cell.

Dennis wrote:

Using XL 2003 & 2000

OK, with help, I was able to utilize Steve Bullen's
FilterCriteria()
Function.

That said, apparently the only way to refresh the data in the
display
cell
is to re-activate the formula by pressing <ENTER in the Fx Box.

Can the display cells be made to refresh in real-time or, second
best,
by F9?

FYI (Background Info):

************************************************** **********II
placed
this
formula into cell A1: =FilterCriteria(A3)
Cell A3 contains the Autofilter down-arrow for column A
************************************************** ***********
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen via j-walk.com
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function
************************************************** ***********

Also, why does this formula not refresh as do other formulas in
the
worksheet? Is it because this particlar Function must be
"force-called"
for
each refresh?

TIA Dennis

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson