autofilter show all data

[RN Mark]

Using a macro how can I check a worksheet to see if an autofilter is in place
and then show all data.

 

[Dave Peterson]

With worksheets("Sheet9999")
'to remove the filter and the arrows
.AutoFilterMode = False
'or to just show all the data
If .FilterMode Then
.ShowAllData
End If
End With

 

[Mike H]

Hi,

Like this

With Worksheets("sheet1")
If .FilterMode Then
.ShowAllData
End If
End With


Mike

 

[BG Mark]

Thanks Mike,
it worked a treat

Hi,

Like this

With Worksheets("sheet1")
If .FilterMode Then
.ShowAllData
End If
End With


Mike

 

[BG Mark]

Thanks Dave, I just required the Filtermode function this time, however I
have noticed in both Mike and your replies you have used If and End If where
as I have just used the line 'If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData', without using the End If statement What is the
difference between the two and when and why should I be using End If.

Mark

 

[Dave Peterson]

There are two different styles of the "if" statement.

The single line version:
if a=b then something else somethingelse

and the block version:
if a = b then
something
else
somethingelse
end if

Depending on what you're doing (how complex that something and somethingelse
are), you may be able to use that single line. But you can always use the block
version.

And as a personal preference, I find it easier to read and easier to modify when
I need to add more stuff to either the Then or Else portion.

On the other hand, I'll sometimes use:

if target.cells.count > 1 then exit sub

So it's not a hard and fast rule for me.