CODEWITHSUNDEEP
vbaexcel
kpatel
kpatel

Reputation: 1

Excel vba find value in sheet and delete rows

I have written the below macros below, and it works perfect when there is data (example) 12345 on the SMT02 sheet.

So if 12345 is present, it leaves those rows and deletes the rest of the rows with other data.

But I want it to work in such a way, even if 12345 is not present, then I still want everything else (rows) to be deleted. At the moment it debugs and stops. Can anyone help please?

Dim c As Range
Dim SrchRng

Sheets("SMT02").Select
Range("B1").Select
Set SrchRng = ActiveSheet.Range("B1", ActiveSheet.Range("B65536").End(xlUp))
Do
    Set c = SrchRng.Find("12345", LookIn:=xlValues)
    If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

Upvotes: 0

Views: 4772

Answers (1)

sallyapplepie
sallyapplepie

Reputation: 11

If you are trying to delete each row in a worksheet when the value "12345" is not present in the cells of a specified column (column B in your example) on a specific sheet (SMT02 in your example) I think you will find that this works:

Sub DeleteRowIfNot12345()

    Dim Rng As Range
    Dim x As Long

    Sheets("SMT02").Select

    Set Rng = Range("B1:B" & Range("B65536").End(xlUp).Row)
    For x = Rng.Rows.Count To 1 Step -1
        If InStr(1, Rng.Cells(x, 1).Value, "12345") = 0 Then
            Rng.Cells(x, 1).EntireRow.Delete
        End If
    Next x

End Sub

Upvotes: 1

Related Questions