Deleting rows if range contains value

BJH1995

New Member
Joined
May 20, 2019
Messages
5
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
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
 

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
457
Office Version
365, 2016, 2013
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
You're welcome & thanks for the feedack
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,151
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top