DELETING ROW

[Guest]

hI THERE!I'd like to ask help on how to delete row based on a value entered
in a textbox located in a user form.The userform and the worksheet where the
data to be deleted is stored are located in the same workbook. Example:
After a user input a four digit number it will automatically search in a
specific column of a particular sheet of a workbook. If a match was found the
data will be displayed in there respective control of the form and when an
OKcommand button is clicked the found data will be deleted. Pls.. Help!

 

[merjet]

This will work. It needs modified if the value could be found more
than once and you want to delete all instances.

Dim miRow As Long

Private Sub CommandButton1_Click()
If miRow = 0 Then Exit Sub
If Label2 = "Found!" Then
Sheets("Sheet1").Rows(miRow).Delete
Label2 = ""
End If
miRow = 0
End Sub

Private Sub TextBox1_AfterUpdate()
Dim iFind As Integer

iFind = TextBox1.Value
Do
miRow = miRow + 1
If iFind = Sheets("Sheet1").Range("A" & miRow) Then
Label2.Caption = "Found!"
Exit Sub
End If
Loop Until Sheets("Sheet1").Range("A" & iRow + 1) = ""
Label2 = "Not found."
End Sub

Hth,
Merjet

 

[Guest]

Public cell as Range

Private Sub TextBox1_Change()
Dim rng as Range, rng1 as Range
Dim s as String

if len(Textbox1.Value) = 4 then
if isnumeric(Textbox1.Value) then
set cell = Nothing
Textbox2.value = ""
Textbox3.Value = ""
s = Textbox1.Value
set rng = Worksheets("Data").Range("C:C")
Set rng1 = rng.Find(What:=s, _
After:=rng(rng.count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng1 is nothing then
set cell = rng1
Textbox2.value = cell.offset(0,-2)
Textbox3.Value = cell.offset(0,-1)
end if
end if
end if
End Sub


Private Sub CommandButton1_Click()
if not cell is nothing then
if len(trim(textbox1.value)) = 4 then
Textbox1.Value = ""
Textbox2.Value = ""
Textbox3.Value = ""
cell.EntireRow.Delete
set cell = Nothing
end if
end if
end Sub

 

[Guest]

Thanks Tom! You got what I mean.. I've been waiting for your response cause
I find it easy to understood.