Delete rows if cells value, across all worksheets!?
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Delete rows if cells value, across all worksheets!?
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    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

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    Is your data in proper tables?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

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

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    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

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    Which line gives the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    ws.ShowAllData

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    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

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Delete rows if cells value, across all worksheets!?

    Can you post the code that you are running?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •