Selecting a range based on output of a for loop

Question

I have a section of code where I am looping through to find the highest blank cell in a sheet, then pasting data from another sheet into a range that begins with that first blank cell. Here's the code I have so far:

Sub Button644_Click()

Dim ReferenceCell As Integer
For r = 1 To 2000
    If Sheets("Summary").Range("A" & r).Value = vbNullString Then
        ReferenceCell = r + 1
        GoTo A
    End If
Next r
    A:s = r + 9 
    Sheets("Summary").Range("A" & r:"Q" & s).Select
End Sub

The debugger ends it on the row after A:. I need the code to be similar to "Range("A1:Q" & s)" but rather than "A(1)", it needs to be "A(r)". This code will eventually pastespecial a table that is A1:Q10 from a different sheet, and loop through so every time the macro is run, it checks for the blank cell, then pastes that table underneath the previously pasted table. This is still in the very early stages. Any help would be greatly appreciated.

Thank you!

There's better ways to find the last blank cell or row. For examples see: http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro — Doug Glancy, May 18 '15 at 16:29
I added the A in, though it doesn't affect the code. Thank you for the suggestion on finding the last row, it may be easier than my method — Nick, May 18 '15 at 16:36
Your code includes `.Range("A" & r:"Q" & s)` and the `:` should probably be inside the quotes with another concatenate operator `&`. `.Range("A" & r & ":Q" & s)`. I will second that there are better ways to find the last row. Here is a decent recap: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — Byron Wall, May 18 '15 at 16:38

score 0 · Answer 1 · answered May 18 '15 at 16:42

ANSWERED: "Your code includes .Range("A" & r:"Q" & s) and the : should probably be inside the quotes with another concatenate operator &. .Range("A" & r & ":Q" & s). I will second that there are better ways to find the last row. Here is a decent recap: stackoverflow.com/questions/11169445/… – Byron"

Selecting a range based on output of a for loop

1 Answers1