CODEWITHSUNDEEP
vbaexcelexcel-formula
Denny
Denny

Reputation: 23

Copy Paste Range using LastRow

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: enter image description here

Thanks a lot guys

Upvotes: 1

Views: 1129

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 2

Related Questions