Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows within a named range
I have the following code
For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then r.EntireRow.Delete End If End If Next r Let's say myRange.address = B5:B23 If row 6 is deleted, the next r.address that's reviewed is B7, not a repeat of B6. What do I need to do to fix this? Thanks, Barb Reinhardt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows within a named range
Don't delete rows within the For loop. Instead, build a new range in the
loop and delete afterwards Sub dural() Dim rr As Range Set rr = Range("A65536") For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then Set rr = Union(rr, r) End If End If Next r rr.EntireRow.Delete End Sub -- Gary''s Student - gsnu200719 "Barb Reinhardt" wrote: I have the following code For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then r.EntireRow.Delete End If End If Next r Let's say myRange.address = B5:B23 If row 6 is deleted, the next r.address that's reviewed is B7, not a repeat of B6. What do I need to do to fix this? Thanks, Barb Reinhardt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows within a named range
You could iterate through your for loop using an integer and the count
of items in your range. Something like: Sub tester() Dim myRange As Range, r As Range Dim i As Integer Set myRange = Range("B5:B23") For i = 1 To myRange.Count For Each r In myRange If r.Text = "status" Then r.EntireRow.Delete i = i - 1 End If Next r Next i End Sub Of course, I am programatically creating the myRange dimension, but a named range should work the same way. HTH -Jeff- Barb Reinhardt wrote: I have the following code For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then r.EntireRow.Delete End If End If Next r Let's say myRange.address = B5:B23 If row 6 is deleted, the next r.address that's reviewed is B7, not a repeat of B6. What do I need to do to fix this? Thanks, Barb Reinhardt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows within a named range
Oops. I forgot to add that I changed the code around a bot for
testing purposes. You would ned something like this: Sub tester() Dim myRange As Range, r As Range Dim i As Integer Set myRange = Range("B5:B23") For i = 1 To myRange.Count For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then r.EntireRow.Delete End If End If Next r Next i End Sub JW wrote: You could iterate through your for loop using an integer and the count of items in your range. Something like: Sub tester() Dim myRange As Range, r As Range Dim i As Integer Set myRange = Range("B5:B23") For i = 1 To myRange.Count For Each r In myRange If r.Text = "status" Then r.EntireRow.Delete i = i - 1 End If Next r Next i End Sub Of course, I am programatically creating the myRange dimension, but a named range should work the same way. HTH -Jeff- Barb Reinhardt wrote: I have the following code For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then r.EntireRow.Delete End If End If Next r Let's say myRange.address = B5:B23 If row 6 is deleted, the next r.address that's reviewed is B7, not a repeat of B6. What do I need to do to fix this? Thanks, Barb Reinhardt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows within a named range
Thanks. I'll do that.
"Gary''s Student" wrote: Don't delete rows within the For loop. Instead, build a new range in the loop and delete afterwards Sub dural() Dim rr As Range Set rr = Range("A65536") For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then Set rr = Union(rr, r) End If End If Next r rr.EntireRow.Delete End Sub -- Gary''s Student - gsnu200719 "Barb Reinhardt" wrote: I have the following code For Each r In myRange If LCase(oWS.Name) = "status" Then Debug.Print r.Address, myRange.Address If r.Text < fNameNew Then r.EntireRow.Delete End If End If Next r Let's say myRange.address = B5:B23 If row 6 is deleted, the next r.address that's reviewed is B7, not a repeat of B6. What do I need to do to fix this? Thanks, Barb Reinhardt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting and deleting named range based on cell value | Excel Programming | |||
how do i maintain named range addressing after deleting row? | Excel Programming | |||
How to add rows(cells) to a named range | Excel Programming | |||
Deleting named ranges by looping through range collection | Excel Programming | |||
Deleting a named range in VBA | Excel Programming |