Delete rows if cells value, across all worksheets!?

GassySpudz

New Member
Joined
Apr 30, 2019
Messages
10
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
 
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
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Is your data in proper tables?
 
Upvote 0
Yes it will, try
Code:
   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
 
Upvote 0
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
 
Upvote 0
Which line gives the error?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Can you post the code that you are running?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top