Deleting rows if range contains value

BJH1995

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedack
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top