Search code examples
excelvbaformulacopy-paste

Copy formula until blank cell

I would like to write a code in VBA which copies formula from range("A3:H3") to these columns until there is data in column J. I've tried this code:

Sub fill_up()
If Not IsNull(Range("J3:J30000")) Then
Range("A3:H3").Select
Selection.Copy
Range("A4:H30000").PasteSpecial xlPasteFormulas
End If
End Sub

But this way I get filled up the columns until the 30000. row anyways. Another code I wrote for a command button:

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
i = 3
j = 4
If Not IsEmpty(Cells(i, 9)) Then
Range("A3:H3").Select
Selection.Copy
Cells(j, 1).PasteSpecial xlPasteFormulas
Do until j > 30000
i = i + 1
j = j + 1
Loop
End If
End Sub

But here the formula is only pasted to the 4. row.

Any suggestions?

Solution

  • I recommend referencing the last cell containing data using ActiveSheet.Usedrange.Rows.Count property.

    In your case:

    Range("A4:H" & ActiveSheet.UsedRange.Rows.Count)