Delete rows if cells value, across all worksheets!?

[GassySpudz]

Hi All,

I've gotten myself in a right mess, sulked like a child and deleted all my work!

What I would like is the main body of a macro which I will hopefully adjust to what I need and I can call upon the sub within another macro.

I want to be able to delete multiple rows if column A cells contain a certain value (with the ability to add extra criteria in the future), but across ALL sheets!
I managed to get it working for a single sheet, but whilst using this site to get it to do all sheets it kept coming up with errors and during my “fixing” I’ve broken it, threw all my toys out the pram and just deleted everything!

If anyone has a code along these lines I can use to tinker with – and hopefully not break again – that would be much appreciated.

E.G
Delete x all sheets

X 2 A delete
B 2 A Keep
X 5 B Delete

 

[GassySpudz]

The value I've used as test exists multiple times across two separate sheets. It finds and deletes the lines if I manually look afterwards but can't seem to remove the autofilter after this

 

[Fluff]

Is your data in proper tables?

 

[GassySpudz]

Yes, I've just remembered the tables are named, this wouldn't cause an issue would it?

 

[Fluff]

Yes it will, try

   For Each Ws In Worksheets
      If Ws.FilterMode Then Ws.ShowAllData
      Ws.Range("A1").AutoFilter 1, Ary, xlFilterValues
      Ws.AutoFilter.Range.Offset(1).EntireRow.Delete
      Ws.ShowAllData
   Next Ws

 

[GassySpudz]

I must be getting on your nerves, so sorry!

But I'm getting a different error now;

Run-time error'1004':
Method 'showAllData' of object '_Worksheet' failed

 

[Fluff]

Which line gives the error?

 

[GassySpudz]

ws.ShowAllData

 

[Fluff]

As the code has just applied a filter, I don't see why you'd get an error on that line.
Does it happen on the first sheet?

 

[GassySpudz]

It happens not matter what sheet I'm on, but now it doesn't even apply the filter or do anything, just shows that error

 

[Fluff]

Can you post the code that you are running?