Sub ShowAll()
On Error GoTo away
ActiveSheet.ShowAllData
Range("C38").Calculate
away:
End Sub

might be more consistent particularly if you have any change events.


--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Sub ShowAll()
Dim tmp
On Error GoTo away
With ActiveSheet
If .FilterMode Then
.ShowAllData
tmp = .Range("C38").Formula
.Range("C38").Value = ""
.Range("C38").Formula = tmp
End If
End With
away:
End Sub


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"JMay" wrote in message
...
Oopps,,, Bob --this destroys (deletes) my formula

=FilterCriteria($A$6:$A$32) << Needed for the next time I auto-filter..

Any other thought?

Jim



"Bob Phillips" wrote in message
:

Change the ShowAll macro to clear it

Sub ShowAll()
On Error GoTo away
With ActiveSheet
.ShowAllData
.Range("C38").ClearContents
End With
away:
End Sub



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JMay" wrote in message
...
I'm using Stephen Bullen's Function code to populate my Cell C38:
After I Filter my C38 it Displays LGC as it should;
I have a forms button assigned (also below) ShowAll
When I click the button all records are displayed, but my Cell C38
still
Shows the LGC Note that I did add (Line3) Application.Volatile,
but
It didn't help so I commented it out. What can I do to have C38
Display a
blank - as it does if I click on the Column header down-arrow and
select
ALL,, When C38 does away with the LGC and Displays Blank << What
I
want to happen.

TIA,

Jim May

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
'Application.Volatile
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 " & .Criteria
Case xlOr
Filter = Filter & " OR " & .Criteria
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

Sub ShowAll()
On Error GoTo away
ActiveSheet.ShowAllData
away:
End Sub