View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Edwin Tam (MS MVP) Edwin Tam (MS MVP) is offline
external usenet poster
 
Posts: 48
Default Conditional Row Deleting

OK. Assume your delete button is called "CommandButton1". Your TextBoxes are called "TextBox1","TextBox2" and "TextBox3".

Place the following code into your commandbutton's click event.

'---------------------------------------------------
Private Sub CommandButton1_Click()
Dim c
Dim firstAddress As String, tmp As Integer
Dim tmp_array()

With Worksheets(2)
Set c = .Range("A1").CurrentRegion.Columns(1).Find(TextBox 1.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
err2:
Exit Sub

err1:
ReDim tmp_array(1)
Resume Next
End Sub
'---------------------------------------------------

----- Todd Huttenstine wrote: -----

I really dont want to do that because the textboxes all
auto-populate based on the value of a combobox, therefor
the user will not have to type anything into the textboxes.



-----Original Message-----
I don't understand why you chose to use that setup, and

require the user to enter three perfect matches in order
to delete. It's very tiring for the user.
How about use a multi-column listbox to display your

data, and after the user has select an item in the list,
and press the delete button, the row in the spreadsheet is
deleted?
1) Insert a ListBox onto your userform. Call it ListBox1.

2) Select the ListBox1 and in Properties window, set

the "ColumnCount" to 3.
3) You got a Delete button called CommandButton1.
4) Place the following code into your userform
'---------------------------------------------

Private Sub CommandButton1_Click()
With ListBox1
If .ListCount 0 Then
Worksheets(2).Rows(.ListIndex + 2).Delete
.RemoveItem .ListIndex
End If
End With
End Sub
Private Sub UserForm_Activate()

With Worksheets(2).Range("A1").CurrentRegion
If .Rows.Count 1 Then
ListBox1.List() = .Offset(1, 0).Resize

(.Rows.Count - 1, 3).Value
ListBox1.ListIndex = 0
End If
End With
End Sub
'---------------------------------------------
.