Vb deleting more than asked to

Dsr122076

New Member
Joined
Feb 16, 2012
Messages
13
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
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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!!!.
 
Last edited:
Upvote 0
Final working delete row based on info typed in userform2.name2 text box.

:eek: 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



:biggrin: SPECIAL THANKS TO ANDREW POLUSUM and TAUREAN for their help with this.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top