CODEWITHSUNDEEP
excelvba
Alegro
Alegro

Reputation: 7966

How to loop till a specific named range?

I want to delete all rows between named ranges rbTop and rbBottom 

Range("rbBottom").Value = ""
Range("rbTop").Select
Do While (Selection.Offset(1, 0).Value <> "")
Selection.Offset(1, 0).EntireRow.Delete
Loop

This works, but I want a slightly shorter way

Range("rbTop").Select
Do While (Selection.Offset(1, 0).Name <> "rbBottom")  // how to write this line ?
Selection.Offset(1, 0).EntireRow.Delete
Loop

Upvotes: 3

Views: 111

Answers (1)

brettdj
brettdj

Reputation: 55702

Without any Select or loops:

Sub Cull()
Dim rng1 As Range
Set rng1 = Range(Range("rbtop"), Range("rbbottom"))
If rng1.Rows.Count > 2 Then rng1.Offset(1, 0).Resize(rng1.Rows.Count - 2, 1).EntireRow.Delete
End Sub

Upvotes: 4

Related Questions