PDA

View Full Version : Solved: Problem Deleting Rows



zoom38
06-24-2006, 05:48 AM
I have a sub that locates data(a name) in column A and then deletes the entire row. My problem if I have four names/rows to delete, it will only delete two of them. I believe it is due to the way Im doing it. I count the total # of rows, use that as my range and then when my routine finds a name to delete it will delete the row, effecting my range but Im not sure what is happening. I'm attaching my file. The problem is in module 7. If you look in module 7 Im copying the names from the first worksheet ("Totals") and pasting to the last worksheet ("Monthly Tally"). It probably would be easier to not paste the the ones I don't want (Promoted and Transferred) but I couldn't figure out how to do it so I went the way you see it in module 7. Im sure it is an easy solution for the experienced Excel users but not so easy for rookies like myself.
Please take a look.
Thanks
Gary

Norie
06-24-2006, 06:06 AM
Gary

Try this.

Sub RemovePromotedAndTransferredMembers()
Dim LastRow As Long
Dim c As Range
Dim I As Long

With Sheets("Monthly Tally").Select
'Find The Last Row On The "Monthly" Worksheets
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
'Find Promoted And Transferred Members And Delete The Entire Row
For I = LastRow To 5 Step -1 'Each c In Range("A5:A" & LastRow)
Set c = .Range("A" & I)
If Left(c, 8) = "Promoted" Then
c.EntireRow.Delete

ElseIf Left(c, 11) = "Transferred" Then
c.EntireRow.Delete
End If
Next I
End With
End Sub

zoom38
06-24-2006, 06:26 AM
Thanks Norie, works great. It never occured to me to go from the bottom up.

Gary