delete entire row when value is found

**9599lorenzo** · 10-13-2015, 09:15 AM

dear all,

below part of my code inside a user form .
I am trying ,once the value is found to delete the entire row but no success (get an error on the line "hawb2.entirerow.delete").
can someone help ?

dim hawb2 As Range
Dim answer As Integer
test1 = TextBox2.Value
wk = ActiveWorkbook.Name
 
    Set hawb2 = ActiveSheet.Range("b:b").Find(What:=test1, LookIn:=xlFormulas, LookAt:=xlWhole)

     If Not hawb2 Is Nothing Then
     answer = MsgBox("awb already exist in " & wk & " row " & hawb2.Row & vbNewLine & "would you like to delete it ?", vbYesNo + vbQuestion)
     If answer = vbYes Then
     hawb2.EntireRow.Delete

     MsgBox "AWB DELETED"
     Exit Sub
     End If
     If answer = vbNo Then
     Exit Sub
     End If
     
     End If

**MarvinP** · 10-13-2015, 09:29 AM

Hi Lorenzo,

"EntireRow.Delete" is supposed to come after a range (or a cell) in most cases. See: https://msdn.microsoft.com/en-us/lib.../ff836836.aspx

How about something like this to do the job?

Sub DelRowTest1()
    Dim LastBRow As Double
    Dim RowCtr As Double
    
    LastBRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For RowCtr = LastBRow To 1 Step -1
        If Cells(RowCtr, "B").Text = "test1" Then
            Cells(RowCtr, "B").EntireRow.Delete
        End If
    Next RowCtr
End Sub

**9599lorenzo** · 10-14-2015, 03:06 AM

Dear MArvin,

tks so much for your suggestion..f.y.I my code works..problem was the fact that I do not realized sheet was protected and this is why I got the error....

delete entire row when value is found

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

delete entire row when value is found

Re: delete entire row when value is found

Re: delete entire row when value is found

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Delete Entire Row if Blank is Found in Column

Delete cell and adjacent column if the same data is found is found in another column

delete the entire row if duplicate found in column

[SOLVED] If Or statement to check cell text and delete entire row if text is found

[SOLVED] Macro To Delete Entire Row If Dublicate Is Found

delete entire row if 'value' found anywhere in column - most efficient way?

Need to compare 1 row in two worksheets and delete entire column if a match not found

Bookmarks

Bookmarks

Posting Permissions