Previous Topic Next Topic

Insert row with formats and formulas

[Closed]
Report
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
 -
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
 -
Hello,

I am curretly trying to write a macro, that will insert a row on the next available line, keeping the formulas, formats, (including some condition formating) but clear the data entered

Can this be done....

My copy row is A5:AI5

Nothing else special about the sheet...

Any help wil be greatly received

Thanks
Debs


Related:

2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
ClearContents will clear formulas and text both leaving format alone
Since you dont want to clear some text, you have to clear it cell by cell
Answer 2 / 2
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
 486
Hi Debs,

Make a save first, if you are unhappy with result you can reload your file.

I assumed that if a row contains data, column A is always filled.

Here is the code: 
Sub InsertAndClearRow()
Dim RowNumber As Integer

RowNumber = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A5:AI5").Copy
Range("A" & RowNumber).insert Shift:=xlDown

For Each cell In Range("A" & RowNumber & ":AI" & RowNumber)
    If Left(cell.Formula, 1) <> "=" Then cell.Value = vbNullString
Next cell

Application.CutCopyMode = False
End Sub


Best regards,
Trowa
Ask a question