Copying formula

Resolved · Low Priority · Version 2013

Tue 17 Jul 2018, 15:43 Delegate Anna said...

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

Tue 17 Jul 2018, 20:23 Trainer Jens said...

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

Wed 18 Jul 2018, 11:40 Delegate Anna said...

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

Wed 18 Jul 2018, 18:47 Trainer Jens said...

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

Wed 25 Jul 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Concatenating Results of Formulas

To concatenate the results of formulas simply add the "&" after the formula or function closing bracket.

function1(....)&function2(.....)

see example Creating a range of monthly payments as text.

View all Excel hints and tips