Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / August 2007

Tip: Looking for answers? Try searching our database.

Deleting Rows Based Upon ListBox Selection

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Paige - 23 Aug 2007 17:02 GMT
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
Tom Ogilvy - 23 Aug 2007 17:12 GMT
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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 28 lines]
> Unload ImportingSchedules
> End Sub
Paige - 23 Aug 2007 17:38 GMT
Thanks, Tom.  However, am getting a 'Type Mismatch' error on the 'Set lrow
=...' line of code.  What would that be due to?

> Private Sub OKButton_Click()
> Dim i As Long, j As Long
[quoted text clipped - 55 lines]
> > Unload ImportingSchedules
> > End Sub
Dave Peterson - 23 Aug 2007 18:52 GMT
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

> Thanks, Tom.  However, am getting a 'Type Mismatch' error on the 'Set lrow
> =...' line of code.  What would that be due to?
[quoted text clipped - 62 lines]
> > > Unload ImportingSchedules
> > > End Sub

Signature

Dave Peterson

Paige - 23 Aug 2007 17:58 GMT
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.

> Private Sub OKButton_Click()
> Dim i As Long, j As Long
[quoted text clipped - 55 lines]
> > Unload ImportingSchedules
> > End Sub
Tom Ogilvy - 23 Aug 2007 21:04 GMT
as Dave said, remove the SET statement from that line.  It is residue left
over from editing your code.  

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 59 lines]
> > > Unload ImportingSchedules
> > > End Sub
Paige - 23 Aug 2007 21:38 GMT
Thanks, guys!!!! Works great now.  Really really appreciate the help.

> as Dave said, remove the SET statement from that line.  It is residue left
> over from editing your code.  
[quoted text clipped - 62 lines]
> > > > Unload ImportingSchedules
> > > > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.