For loop to select entire rows within the loop

Regular Contributor

Hi All,


I would like to select all the from rows 6 to rows 9. However, the for loop below somehow selects only row 9.


Therefore, how do i amend the code below so that row 6 to row 9 is selected?


Dim ws              As Worksheet
Dim find_no         As Long
Dim find_period     As Long

Set ws = Sheet1
find_no = ws.Range("A:A").Find(what:="*No").Row + 1
find_period = ws.Range("A:A").Find(what:="Period").Row - 2

With ws
    For i = find_no To find_period
        Range("A" & i).EntireRow.Select
    Next i
End With

Debug.Print find_no 'shows row 6
Debug.Print find_period ' shows row 9
End Sub


thanks and appreciate the help in advance!Capture.PNG

2 Replies
best response confirmed by hrh_dash (Regular Contributor)

Replace the for loop with this. I'm not very familiar VBA so it isn't clean, but it works.


With ws
        Range("A" & find_no & ":A" & find_period).EntireRow.Select
End With





,it works perfectly. thanks for the help!