Delete rows if cells value, across all worksheets!?

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
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
33,967
Office Version
365
Platform
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
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
33,967
Office Version
365
Platform
Windows
Which line gives the error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
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
33,967
Office Version
365
Platform
Windows
Can you post the code that you are running?
 

Forum statistics

Threads
1,086,107
Messages
5,387,872
Members
402,085
Latest member
tlemke

Some videos you may like

This Week's Hot Topics

Top