Find second empty cell/row and starting pasting from it

Question

I've already did a lot of research and couldn't find a solution. I'm trying to find the second empty cell/row (or other specified cell) in a column and start pasting from it (always jumping to next empty cell/row).

I found the code below, but it starts pasting from the first empty row in a column. Changing the offset command to (2,0) doesn't work too because it finds the second empty row but starts pasting always leaving a empty cell between the collages. And I want to find the second empty cell only in the beginning and from there start pasting always in the next first empty cell/row. Can you guys help me please? Thanks a lot in advance!

For example, I'm copying the range G4:I4 and trying to paste into column G.

Code:

Sub InsertButton() 
    Range("G4:I4").Copy Range("G" & Rows.Count).End(xlUp).Offset(1, 0) 
End Sub

JonnyAggro JonnyAggro · Accepted Answer · 2014-09-18T03:14:20

Well, you're trying to write one function that does two different things. If you could include the loop that calls the function, that would be helpful. You could use two functions like this that do each of the things, or pass a parameter to the function that would tell it which thing to do.

Sub InsertButton1()
    Range("G4:I4").Copy Range("G" & Rows.Count).End(xlUp).Offset(1, 0) 
End Sub
Sub InsertButton2()
    Range("G4:I4").Copy Range("G" & Rows.Count).End(xlUp).Offset(2, 0) 
End Sub

or

Sub InsertButton(moveDownThisManyRows)
    Range("G4:I4").Copy Range("G" & Rows.Count).End(xlUp).Offset(moveDownThisManyRows, 0) 
End Sub

Then when you call it say

Sub doStuff()
    Dim thisIsTheFirstTime As Boolean
    thisIsTheFirstTime = True
    For Each item In myStuff
        If thisIsTheFirstTime Then
            InsertButton2() 'or InsertButton(2)
            thisIsTheFirstTime = False
        Else
            InsertButton1() 'or InsertButton(1)
        End If
    Next item
End Sub

Find second empty cell/row and starting pasting from it

1 Answers