PDA

View Full Version : ActiveSheet.ShowAllData



uktous
06-28-2012, 08:18 AM
Hi,

If we use advanced filter to filter some data, then we can use ActiveSheet.ShowAllData to show all data.

However, if there is no data filtered (ie all data are shown), then using ActiveSheet.ShowAllData will cause error.

In my macro, is it possible to use ActiveSheet.ShowAllData only when some data are filter, and not use ActiveSheet.ShowAllData when all data are shown.

Thanks

CatDaddy
06-28-2012, 08:44 AM
On Error Resume Next
ActiveSheet.ShowAllData
On Error Goto 0

Bob Phillips
06-28-2012, 08:44 AM
Dim rng As Range

With ActiveSheet

If .AutoFilterMode Then

Set rng = .AutoFilter.Range
If rng.Rows.Count > rng.SpecialCells(xlCellTypeVisible).Rows.Count Then

.ShowAllData
End If
End If
End With