clear Autofilter if on

[howard]

I have data on sheet2 and have written code to clear filter if on, but the code does not do this

It would be appreciated if some would test my code and amend

Sub Clear_Data()
With Sheets(2)
Dim LR As Long
LR = .Cells(.Rows.Count, "A").End(xlUp).Row

Sheets(2).Select

If ActiveSheet.FilterMode Then
Cells.autofilter
End If

   
.Range("A2:AZ" & LR).ClearContents
End With

   
End Sub

 

[RoryA]

If you aren't dealing with Tables, you can just use:

On error resume next
Sheets(2).Showalldata
On error goto 0

 

[howard]

Thanks for the help Rory


I have just picked up that some of the data is not being cleared from row 10678.


Please check my code & advise

 

[RoryA]

Your code assumes that you can use column A to determine the last row of data. It sounds like that is not the case, so you could use this instead:

LR = .Usedrange.Row + .Usedrange.Rows.Count - 1

 

[howard]

Thanks Rory. Something new I have learnt

 

[RoryA]

Something new I have learnt

A good day then!