Insert row with formats and formulas

Closed
Debs269 Posts 21 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 - Feb 1, 2013 at 11:32 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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


Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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