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

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

--
Regards,
Tom Ogilvy

"Paige" wrote:

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

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

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Paige" wrote:
>
> > 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
> >

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.

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Paige" wrote:
>
> > 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
> >

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



Paige wrote:
>
> Thanks, Tom. However, am getting a 'Type Mismatch' error on the 'Set lrow
> =...' line of code. What would that be due to?
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Paige" wrote:
> >
> > > 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
> > >

--

Dave Peterson

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

--
Regards,
Tom Ogilvy


"Paige" wrote:

> 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.
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Paige" wrote:
> >
> > > 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
> > >

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

"Tom Ogilvy" wrote:

> as Dave said, remove the SET statement from that line. It is residue left
> over from editing your code.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Paige" wrote:
>
> > 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.
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Paige" wrote:
> > >
> > > > 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
> > > >