Deleting Rows Based Upon ListBox Selection
 

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

Deleting Rows Based Upon ListBox Selection
 

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

Deleting Rows Based Upon ListBox Selection
 

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

Deleting Rows Based Upon ListBox Selection
 

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

Deleting Rows Based Upon ListBox Selection
 

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

Deleting Rows Based Upon ListBox Selection
 

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

Deleting Rows Based Upon ListBox Selection
 

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