Macro to delete rows if certain condition is met

**mjali001** · 12-19-2011, 12:24 PM

Hello guys I am trying to build a macro that will delete the entire row if a certain value appears in a certain column. For example lets say it goes down column F, I will need the macro to delete entire rows if there is no "1" present. Is creating a macro like this possible. Thanks in advance for the help, I've also posted a sample of the data.

**arthurbr** · 12-19-2011, 12:35 PM

You can probably tweak the solution prvided by Mudraker here ( I don't have access to XL right now)

**Charles** · 12-19-2011, 12:45 PM

HI,

The following code may help. Copy to a module and change sheet name to the sheet name in your file.
Test on copy only.

Sub FilterDeleteNon1()
Application.ScreenUpdating = False
Dim VisRng As Range, cell As Range
Dim Myval As Integer
Dim lrow As Long
lrow = Sheets("Sheet2").Range("G65536").End(xlUp).Row
    Range("A1:G1").Select
        Selection.AutoFilter
        With Selection
            .AutoFilter Field:=7, Criteria1:="<> 1"    '' this set the filtered data for the value
        End With
            With Worksheets("Sheet2").AutoFilter.Range

            Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
                    .Cells.SpecialCells(xlCellTypeVisible)
            '' make sure you have more than 1 row  ''
            Myval = .Range("g2:g" & lrow).SpecialCells(xlCellTypeVisible).Count
            If Myval >= "2" Then
                Range(Cells(VisRng.Offset(1, 0).Row, 1), Cells(Range("A65536").End(xlUp).Row, 4)).EntireRow.Delete
            End If
            End With
    Selection.AutoFilter
End Sub

**arlu1201** · 12-19-2011, 12:57 PM

Another version-

Option Explicit
Dim lrow As Long
Dim i As Long

Sub delete_rows()

lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = lrow To 1 Step -1
    If Range("G" & i).Value <> 1 Then
        Rows(i & ":" & i).Delete
        lrow = lrow - 1
    End If
Next i

End Sub

I am not able to delete the rows containing #VALUE!. Maybe you can take care of it when you have a formula(i guess vlookup) to make it "" or blank instead of #VALUE!.

**kvsrinivasamurthy** · 12-20-2011, 04:20 AM

I have slightly modified the code of Sri arlu1201. This works ok. No modifications necessary. Convey your findings.

Option Explicit
Dim lrow As Long
Dim i  As Long

Sub delete_rows()
lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = lrow To 1 Step -1
    If IsError(Range("G" & i).Value) Then
        Rows(i & ":" & i).Delete
        lrow = lrow - 1
    ElseIf Range("G" & i).Value <> 1 Then
        Rows(i & ":" & i).Delete
        lrow = lrow - 1
        
    End If
Next i

End Sub