Results 1 to 5 of 5

Thread: Deleting rows if range contains value
Thanks Thanks: 0 Likes Likes: 0

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

    Default Deleting rows if range contains value

    Hi,

    I am trying to run the following code below which loops through all worksheets in the workbook apart from 2; which then looks in the range ("I4:I34") in each sheet, deleting the entire row if any of these values is 0.
    The code seems to only delete a few of the rows containing 0 and am unsure as to what this could be if anyone could offer any expertise?

    Sub DelRows

    Dim ws as worksheet
    Dim Check_Range as range
    Dim num as variant



    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Run" And ws.Name <> "Template" Then

    Set Check_Range = Range("I4:I34")
    For Each num In Check_Range
    If num.Value <> 0 Then
    num.EntireRow.Delete
    End If
    Next num

    End If
    Next ws

    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,798
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: Deleting rows if range contains value

    Hi & welcome to MrExcel.
    How about
    Code:
    Sub DelRows()
       Dim ws As Worksheet
       Dim num As Long
       
       For Each ws In ThisWorkbook.Worksheets
          If ws.Name <> "Run" And ws.Name <> "Template" Then
             For num = 34 To 4 Step -1
                If Cells(num, 9).Value <> 0 Then Rows(num).Delete
             Next num
          End If
       Next ws
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular nemmi69's Avatar
    Join Date
    Mar 2012
    Posts
    368
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Deleting rows if range contains value

    It needs to be done in reverse so try

    Code:
    Option Explicit
    Sub DelRows()
    
    
    End Sub
    Dim ws As Worksheet
    Dim Check_Range As Range
    Dim num As Long
    
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Run" And ws.Name <> "Template" Then
            Set Check_Range = ws.Range("I4:I34")
            For num = Check_Range.Rows.Count To Check_Range.Row Step -1
                If ws.Range("I" & num).Value = 0 Then
                    ws.Rows(num).Delete
                End If
            Next num
        End If
    Next ws
    
    
    End Sub
    What doesn't kill you is just biding it's time

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting rows if range contains value

    Thank you very much, worked a treat.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,798
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: Deleting rows if range contains value

    You're welcome & thanks for the feedack
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

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
  •