Modifying Code that deletes a Row

[Todd Huttenstine]

Hey guys

Happy Thanksgiving.

Here is a code(see below) that deletes the entire row if
Textbox1,2,and3 match data found in a row in range A1:A3
starting in A1.

What would the code be, that would put the number of the
row to be deleted in cell K1, instead of actually deleting
the entire Row that matches the criteria in the 3
textboxes? I dont want to delete the row, just put the
row to be deleted in cell K1.


Private Sub CommandButton6_Click()
If MsgBox("Are you sure?", vbYesNo) = vbNo Then
Exit Sub
End If
Dim c
Dim firstAddress As String, tmp As Integer
Dim tmp_array()

With Worksheets(2)
Set c = .Range("A1").CurrentRegion.Columns(1).Find
(TextBox1.Text, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(0, 1).Value = TextBox2.Text And
c.Offset(0, 2).Value = TextBox3.Text Then
On Error GoTo err1
ReDim Preserve tmp_array(UBound(tmp_array)
+ 1)
On Error GoTo 0
tmp_array(UBound(tmp_array)) = c.Address
(False, False)
End If
Set c = .Range("A1").CurrentRegion.Columns
(1).FindNext(c)
Loop While Not c Is Nothing And c.Address <>
firstAddress
On Error GoTo err2
For tmp = UBound(tmp_array) To 1 Step -1
.Range(tmp_array(tmp)).EntireRow.Delete
Next
End If
End With
Unload Me
MsgBox "1 Record Deleted Successfully", vbOKOnly

err2:
Exit Sub

err1:
ReDim tmp_array(1)
Resume Next
End Sub




Thanx,
Todd

 

[Bob Phillips]

Todd,

This code

For tmp = UBound(tmp_array) To 1 Step -1
.Range(tmp_array(tmp)).EntireRow.Delete
Next

can be replaced by

For tmp = UBound(tmp_array) To 1 Step -1
Range("K1") = .Range(tmp_array(tmp)).Row
Next

but it will get over-written if you find more than 1 occurence.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

Thank you

Todd

-----Original Message-----
Todd,

This code

For tmp = UBound(tmp_array) To 1 Step -1
.Range(tmp_array(tmp)).EntireRow.Delete
Next

can be replaced by

For tmp = UBound(tmp_array) To 1 Step -1
Range("K1") = .Range(tmp_array(tmp)).Row
Next

but it will get over-written if you find more than 1 occurence.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.