Insert row with formats and formulas

Closed
Debs269
Posts
23
Registration date
Monday October 15, 2012
Status
Member
Last seen
August 18, 2016
- Feb 1, 2013 at 11:32 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Feb 5, 2013 at 12:00 PM
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


2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 2, 2013 at 03:05 PM
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
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Feb 5, 2013 at 12:00 PM
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
0