Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

For loop to select entire rows within the loop

Iron 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 (Iron Contributor)
Solution

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

 

plsfix_0-1667881352722.png

 

 

,it works perfectly. thanks for the help!
1 best response

Accepted Solutions
best response confirmed by hrh_dash (Iron Contributor)
Solution

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

 

plsfix_0-1667881352722.png

 

 

View solution in original post