Copy Paste Range using LastRow

Question

I have a question on how to copy a formula from a certain specified range to a specified FirstRow to LastRow if from A7 onward until LastRow the rows have data.

If from A7 until LastRow has Data, the formula from H6:J6 should be pasted as formula from H7:J7 until LastRow.

Now the problem is if from A7 onward the rows are empty, it will copy the formula in H5:J5. Is there a code I can use so that if A7 onward is empty that no formula is copied at all? Perhaps Define the FirstRow as fixed or something.

Sub CopyFormulaIF()

Dim myLastRow As Long
Dim myCol As Long
Dim WB As Workbook
Dim WS As Worksheet

Set WB = ThisWorkbook
Set WS = WB.Sheets("Tabelle1")

'Screen update in 0 seconds
Application.ScreenUpdating = 0

With WS
    myLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

For myCol = 8 To 10                                              
    Cells(6, myCol).Copy                                        
    Range(Cells(7, myCol), Cells(myLastRow, myCol)).PasteSpecial Paste:=xlFormulas 
Application.CutCopyMode = False                             
Next myCol

End Sub

Sample:

Thanks a lot guys

Did you check if myLastRow returns the correct value? I see from the sample that A2, A3 and A6 are empty, so your myLastRow value is probably 3 cells short. — VBA Pete

user3598756 user3598756 · Accepted Answer · 2017-01-15T13:46:47

you could do this

Option Explicit

Sub CopyFormulaIF()
    Dim myLastRow As Long
    Dim myCol As Long

    'Screen update in 0 seconds Application.ScreenUpdating = 0

    With ThisWorkbook.Sheets("Tabelle1")
        myLastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        If myLastRow < 7 Then Exit Sub

        For myCol = 8 To 10
            .Range(.Cells(7, myCol), .Cells(myLastRow, myCol)).FormulaR1C1 = .Cells(6, myCol).FormulaR1C1
        Next
    End With
End Sub

Copy Paste Range using LastRow

1 Answers