Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Greetings all. I am trying to delete some rows based on whether or not the
right 4 characters in the cell = "View". I am using the following... LstRow = [B5000].End(xlUp).Row Set Myrng = Range("B1:B" & LstRow) For Each MyCell In Myrng If Right(MyCell, 4) = "View" Then Rows(MyCell.Row).EntireRow.Delete End If Next MyCell The problem is that I have to run it multiple times to delete all the rows ending in "View". In column B there are 8 rows of records ending in "View", then three records not ending in "View", for a total of 11 records per id value in column A, and this pattern is continuous all the way to around row 4000 or so. When I run it the first time, it deletes the first three rows in each group. The second time I run it, it deletes the next two records from each group, and then I have to run it three more times to delete the last three records from each group. Any ideas why it is doing this? Thank you. Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
On May 18, 11:36*am, Greg Snidow
wrote: Greetings all. *I am trying to delete some rows based on whether or not the right 4 characters in the cell = "View". *I am using the following... * * LstRow = [B5000].End(xlUp).Row * * Set Myrng = Range("B1:B" & LstRow) * * For Each MyCell In Myrng * * * * If Right(MyCell, 4) = "View" Then * * * * * * Rows(MyCell.Row).EntireRow.Delete * * * * End If * * Next MyCell The problem is that I have to run it multiple times to delete all the rows ending in "View". *In column B there are 8 rows of records ending in "View", then three records not ending in "View", for a total of 11 records per id value in column A, and this pattern is continuous all the way to around row 4000 or so. *When I run it the first time, it deletes the first three rows in each group. *The second time I run it, it deletes the next two records from each group, and then I have to run it three more times to delete the last three records from each group. *Any ideas why it is doing this? *Thank you. Greg Greg, As you delete rows, your range changes each time a row is deleted. Thus as you cycle from top to bottom, you never actually reach the bottom. (See the RowDeletion procedure below. Run this procedure via F8 repeatedly, and watch the Immediate Window. You can also put dummy data (such as 1, 2, 3, 4, 5, respectively into the rows in column A in a worksheet and watch both Excel and the Immediate window as you press F8). Use a For Loop with Step - 1 rather than the For Each Loop. (See RowDeletionStep1 below). I hope this helps. Best, Matthew Herbert Sub RowDeletion() Dim Rng As Range Dim rngCell As Range Set Rng = Range("A1:A5") For Each rngCell In Rng.Cells Debug.Print "Eval. Cell :"; rngCell.Address Debug.Print "Pre-deletion :"; Rng.Address rngCell.EntireRow.Delete Debug.Print "Post-deletion:"; Rng.Address Next End Sub Sub RowDeletionStep1() Dim lngLastRow As Long Dim lngFirstRow As Long Dim lngL As Long lngLastRow = Range("B5000").End(xlUp).Row lngFirstRow = 1 For lngL = lngLastRow To lngFirstRow Step -1 If Right(Range("B" & lngL), 4) = "View" Then Range("B" & lngL).EntireRow.Delete End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Thank you so much Mathew for the quick response. I never thought of starting
at the bottom, but now it make sense. Greg " wrote: On May 18, 11:36 am, Greg Snidow wrote: Greetings all. I am trying to delete some rows based on whether or not the right 4 characters in the cell = "View". I am using the following... LstRow = [B5000].End(xlUp).Row Set Myrng = Range("B1:B" & LstRow) For Each MyCell In Myrng If Right(MyCell, 4) = "View" Then Rows(MyCell.Row).EntireRow.Delete End If Next MyCell The problem is that I have to run it multiple times to delete all the rows ending in "View". In column B there are 8 rows of records ending in "View", then three records not ending in "View", for a total of 11 records per id value in column A, and this pattern is continuous all the way to around row 4000 or so. When I run it the first time, it deletes the first three rows in each group. The second time I run it, it deletes the next two records from each group, and then I have to run it three more times to delete the last three records from each group. Any ideas why it is doing this? Thank you. Greg Greg, As you delete rows, your range changes each time a row is deleted. Thus as you cycle from top to bottom, you never actually reach the bottom. (See the RowDeletion procedure below. Run this procedure via F8 repeatedly, and watch the Immediate Window. You can also put dummy data (such as 1, 2, 3, 4, 5, respectively into the rows in column A in a worksheet and watch both Excel and the Immediate window as you press F8). Use a For Loop with Step - 1 rather than the For Each Loop. (See RowDeletionStep1 below). I hope this helps. Best, Matthew Herbert Sub RowDeletion() Dim Rng As Range Dim rngCell As Range Set Rng = Range("A1:A5") For Each rngCell In Rng.Cells Debug.Print "Eval. Cell :"; rngCell.Address Debug.Print "Pre-deletion :"; Rng.Address rngCell.EntireRow.Delete Debug.Print "Post-deletion:"; Rng.Address Next End Sub Sub RowDeletionStep1() Dim lngLastRow As Long Dim lngFirstRow As Long Dim lngL As Long lngLastRow = Range("B5000").End(xlUp).Row lngFirstRow = 1 For lngL = lngLastRow To lngFirstRow Step -1 If Right(Range("B" & lngL), 4) = "View" Then Range("B" & lngL).EntireRow.Delete End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming |