Deleting Rows Based Upon ListBox Selection

[Guest]

Option2ListBox populates with the items contained in Col H of the 'Schedules'
tab; the user can then select 1 or more of these items. For each selected
item, I want Excel to go to the 'Schedules' tab and delete any row that has
that item in Col H. Problem is that the code is not consistently deleting
all the corresponding rows for those items selected in the listbox. This
must be something simple I'm overlooking. Can anyone help???? Also, is
there a quicker way to delete these rows? Am a little concerned about speed,
since there's likely to be several thousand rows to look through.

Private Sub OKButton_Click()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cells As Range

j = 0

For i = 0 To Me.Option2ListBox.ListCount - 1
If Me.Option2ListBox.Selected(i) = True Then
With worksheets("Schedules")
Set rngToSearch = .Range(.Range("H1"), .cells(Rows.Count,
"H").End(xlUp))
End With
For Each rng In rngToSearch
If rng = Me.Option2ListBox.List(i) Then
rng.EntireRow.Delete
End If
Next
j = j + 1
End If
Next i
Unload ImportingSchedules
End Sub

 

[Guest]

Private Sub OKButton_Click()
Dim i As Long, j As Long
Dim ws As Worksheet
Dim cells As Range

j = 0

For i = 0 To Me.Option2ListBox.ListCount - 1
If Me.Option2ListBox.Selected(i) = True Then
With worksheets("Schedules")
Set lrow = .cells(Rows.Count,"H").End(xlUp).row
set r = .Range("H1").Resize(lrow,1)
End With
if application.Countif(r,Me.Option2Listbox.List(i)) > 0 then
For k = lrow to 1 step -1
set rng = worksheets("Schedules").Cells(k,"H")
If rng = Me.Option2ListBox.List(i) Then
rng.EntireRow.Delete
End If
Next
End if
j = j + 1
End If
Next i
Unload ImportingSchedules
End Sub

 

[Guest]

Thanks, Tom. However, am getting a 'Type Mismatch' error on the 'Set lrow
=...' line of code. What would that be due to?

 

[Guest]

Did some digging re the type mismatch; was due to the fact that I hadn't
referenced the correct VBA extensibility library. So now don't get the error
message; however, it does not delete any applicable rows.

 

[Dave Peterson]

Try removing the word "Set" from this line:

Set lrow = .cells(Rows.Count,"H").End(xlUp).row
so it's just:
lrow = .cells(Rows.Count,"H").End(xlUp).row

 

[Guest]

as Dave said, remove the SET statement from that line. It is residue left
over from editing your code.

 

[Guest]

Thanks, guys!!!! Works great now. Really really appreciate the help.