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
 

GassySpudz

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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,217
Office Version
  1. 365
Platform
  1. Windows
Is your data in proper tables?
 

GassySpudz

New Member
Joined
Apr 30, 2019
Messages
10
Yes, I've just remembered the tables are named, this wouldn't cause an issue would it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,217
Office Version
  1. 365
Platform
  1. Windows
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
 

GassySpudz

New Member
Joined
Apr 30, 2019
Messages
10

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,217
Office Version
  1. 365
Platform
  1. Windows
Which line gives the error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,217
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,217
Office Version
  1. 365
Platform
  1. Windows
Can you post the code that you are running?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,816
Messages
5,655,462
Members
418,204
Latest member
ElizabethCorrin

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
Top