Previous Topic Next Topic

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

Answer 1 / 2
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
0
Answer 2 / 2
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
0