Anna has attended:
Excel Advanced course
Copying formula
Hi,
I'm using this formula to add a new row beneath existing rows of data:
Sub helping()
Dim count As Long
For count = ActiveSheet.UsedRange.Rows.count To 1 Step -1
If Information.IsEmpty(Cells(count, 1)) = True Then Rows(count + 1).Insert
Next count
End Sub
This is working fine, but I'd also like it to copy down the formula from the last row with content into the new row that's been added.
Would this be possible?
Thanks,
Anna
RE: Copying formula
Hi Anna,
Thank you for the forum question.
You have not said anything about in which columns you have the formulas you want to copy down.
In the code below I copy down formulas in column A and B. If you have the formulas in other columns you will need to amend the code.
Sub helping()
Dim count As Long
For count = ActiveSheet.UsedRange.Rows.count To 1 Step -1
If Information.IsEmpty(Cells(count, 1)) = True Then
Rows(count + 1).Insert
Range("a" & count - 1, "b" & count - 1).Select
Selection.AutoFill Destination:=Range("A" & count - 1, "B" & count), Type:=xlFillDefault
End If
Next count
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Copying formula
Hi Jens,
Apologies for not providing that level of detail.
The formulas are across a lot of columns - in most cases in columns A - AG, but sometimes running all the way over to CR.
What would be the easiest way to update that code?
Thanks,
Anna
RE: Copying formula
Hi Anna,
Please try the code below.
Sub helping()
Dim count As Long
Dim ColNum As Integer
ColNum = ActiveSheet.UsedRange.Columns.count
For count = ActiveSheet.UsedRange.Rows.count To 1 Step -1
If Information.IsEmpty(Cells(count, 1)) = True Then
Rows(count + 1).Insert
Range(Cells(count - 1, 1), Cells(count - 1, ColNum)).Select
Selection.AutoFill Destination:=Range(Cells(count - 1, 1), Cells(count, ColNum)), Type:=xlFillDefault
End If
Next count
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
