Based on Cell Criteria Delete Row in excel

**AlpAnA$** · 09-07-2011

My question is that I want to delete a row. Delete Row based on Cell Criteria. The function should be similar to as follows:

Delete the corresponding row in the first column with the number, if cell A1 = 3.

An for instance:

Cell a1 contains a number with random output by that of a command button.

Also I have column through 74 that is numbered 1. The Row has to be deleted if the number in cell a1 matches that of a row. Your help will be appreciated.

**Author** · 09-07-2011

Just copy paste the below code in your actual source code. The output will be as per your requirement. The code based on criteria input works to delete a row.

Code:

Range("a1:a74").Select
    For Each cl In Selection
        If cl.Text = ComboBox1.Text Then
            cl.EntireRow.Delete
        End If
    Next

Hope you get the appropriate help from the code.

**Jacques25** · 09-07-2011

I have modified the code more precisely. Use it in your actual source code. Before that refer to it and make some minor changes as per your code. This code is from a webpage with success.

Code:

 Dim DeleteValue As String
    Dim rng As Range    

DeleteValue = ("Randomizer!d3")
    ' This will delete the rows with "ron" in the Range("A1:A100")

    With ActiveSheet
        Sheet9.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
        With ActiveSheet.AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete

        End With
        .AutoFilterMode = False
    End With

**Vandam** · 09-07-2011

If you haven’t found help from the above code than try this. The syntax which I have used is very easy and understandable whenever you refer. I hope you won’t have any question on it . Just take all necessary help from it.

Code:

Sub test()
    Dim maxRow As Long
    Dim testVal As Double
    Dim i As Long
    
    Rem find last row in SBS Col A
maxRow = Sheets("SBS").Range("a65536").End(xlUp)
    Rem get random value from Randomizer
testVal = Sheets("Randomizer").Range("a1").Value
    
For i = maxRow To 1 Step -1
    If Sheets("SBS").Cells(i, 1) = testVal Then
        Sheets("SBS").Cells(i, 1).EntireRow.Delete shift:=xlUp
        Exit For
    End If
Next i

End Sub

**Steyn** · 09-07-2011

Also add the below code in your actual source code so that you can set the range of selection. While taking a look on the code you will understand by yourself.

Code:

Range("a1:a74").Select
    For Each cl In Selection
        If cl.Text = ComboBox1.Text Then
            cl.EntireRow.Delete
        End If
    Next

**Shreevats** · 09-07-2011

I would like to add on it. The above code is incomplete so you can’t directly copy paste on the main source code. Just see the below code and see the difference. The above code is excellent but its bit incomplete. Its necessary that you have to mention the string syntax.

Code:

Dim DeleteValue As String
    Dim rng As Range    

DeleteValue = ("Randomizer!d3")
    ' This will delete the rows with "ron" in the Range("A1:A100")

    With ActiveSheet
        Sheet9.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
        With ActiveSheet.AutoFilter.Range

Range("a1:a74").Select
    For Each cl In Selection
        If cl.Text = ComboBox1.Text Then
            cl.EntireRow.Delete
        End If
    Next