Page 1 of 2 12 LastLast
Results 1 to 10 of 20

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

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

    Lightbulb Delete rows if cells value, across all worksheets!?

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Hi & welcome to MrExcel.
    Will the criteria be exact values, or will it be partial matches?
    - 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. #3
    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!?

    Hi!

    Thank for the fast response! It will be exact values I will manually edit in the macro. I would like to do it in a data range in the future so less capable bodies could edit, though i'll figure that out myself if you know if its even possible?

    Zac

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Ok, how about
    Code:
    Sub GassySpudz()
       Dim Ws As Worksheet
       Dim Ary As Variant
       
       Ary = Array("Hertfordshire", "Wiltshire")
       For Each Ws In Worksheets
          If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
          Ws.Range("A1").AutoFilter 1, Ary, xlFilterValues
          Ws.AutoFilter.Range.Offset(1).EntireRow.Delete
          Ws.AutoFilterMode = False
       Next Ws
    End Sub
    Change values in red to suit.
    - 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. #5
    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!?

    Hi and thanks again!

    It is running and deleting the data from one work sheet but showing an error when it tries to remove the filter and the yellow fault line is on the .delete row on VBA

    Zac

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    What is the error message & number?
    - 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. #7
    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!?

    Hi there!

    Sorry for the late reply, please see error below!

    Run-time error '91';

    Object variable or With block variable not set

    Thanks,
    Zac

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Not sure why you would be getting that error.
    Do you have any merged cells?
    Also does your data always start in A1 with a header on row 1?
    - 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. #9
    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!?

    Hello again!

    Sorry about the late reply, I've been in and out of the office!

    No merged cells, and yes A1 is the header and A2 downwards is the start of the data

    Thanks,
    Zac

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Do the value(s) that you are filtering on actually exist in the 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

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
  •