Deleting an entire row based on a cell value?

chuckles1066

Banned
Joined
Dec 20, 2004
Messages
372
I would like to be able to delete an entire row if the value of any cell between row 3 and row 999 in column d equals 0.

I'd like to be able to do this over a range of worksheets, named Jan, Feb, Mar.........Dec.

Is there a quick way?

Many thanks, I am not worthy.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Depending on whether you need to process all sheets in a workbook, or avoid some, I have provided 2 options. Both use the same code to remove unwanted lines, they just loop differently through the sheets.
Code:
Sub ProcessSheets_1()
    'if all sheets in workbook are to be processed
    Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        Sht.Activate
        DeleteLines
    Next Sht
End Sub

Sub ProcessSheets_2()
    'if other sheets must be avoided
    Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name = "Other" Or Sht.Name = "Mar" Then
            'do nothing
        Else
            'process
            Sht.Activate
            DeleteLines
        End If
    Next Sht
End Sub

Sub DeleteLines()
    Dim Rw As Long
    For Rw = 999 To 3 Step -1
        If Cells(Rw, 4).Value = 0 Then Cells(Rw, 4).EntireRow.Delete
    Next Rw
End Sub
Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,367
Members
410,911
Latest member
AniEx
Top