Vb deleting more than asked to

[Dsr122076]

What i need is for the command button to sort through all my worksheets of info (16 so far) match the information in my userform2.name2 text box and delete that row. It needs to do this for each worksheet. This code below already does most of that. However, it has a glitch. if it does not find the match it deletes the first row regardless of what the userform2.name2 says. Also if i have the required text in row 4 it deletes row 1 and row 4. Another glitch it has is that when the msgbox pops up telling you to insert a name because it is blank, it still deletes a row. Please let me know what I can do to fix this. thanks.

Private Sub Delete_Click()
If Userform2.Name2.Text = "" Then MsgBox "please enter name"

For i = 1 To Sheets.Count
With Sheets(i)
.AutoFilterMode = False
With .range("A1:a2000", .range("A" & Rows.Count).End(xlUp))
On Error Resume Next
.AutoFilter 1, Userform2.Name2.Value
.Offset(0).SpecialCells(12).EntireRow.Delete
On Error GoTo 0
End With
.AutoFilterMode = False

End With

Next i
Userform2.Name2.Text = ""



End Sub

 

[Andrew Poulsom]

Don't you need Offset(1) instead of Offset(0)?

 

[Dsr122076]

Don't you need Offset(1) instead of Offset(0)?

When I had that on there it was deleting the row below the text i was searching for. It is driving me insane.

Ok I figured out what the problem was. I did not leave row 1 for headers I had sheets full of data with no headers. Once I added a heade and discounted that row i filled the rest with data and it seems to be working. I did what you said and changed the offset to 1. It seems to be working now, but I will do some additional testing. THANKS!!!.

 

[Dsr122076]

Final working delete row based on info typed in userform2.name2 text box.

MUST HAVE ROW 1 SET AS HEADERS or just ignored. Nothing on row 1 will be taken into account.

Private Sub Delete_Click()
If Userform2.Name2.Text = "" Then MsgBox "please enter name"

For i = 1 To Sheets.Count
With Sheets(i)
.AutoFilterMode = False
With .range("A1:a2000", .range("A" & Rows.Count).End(xlUp))
On Error Resume Next
.AutoFilter 1, Userform2.Name2.Value
.Offset(1).SpecialCells(12).EntireRow.Delete
On Error GoTo 0
End With
.AutoFilterMode = False

End With

Next i
Userform2.Name2.Text = ""



End Sub



SPECIAL THANKS TO ANDREW POLUSUM and TAUREAN for their help with this.