Insert row with formats and formulasClosed

Question

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


Configuration: Windows 7 / Internet Explorer 9.0

rizvisa1 · Answer

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 · Answer

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

Insert row with formats and formulas

2 replies

Subscribe To Our Newsletter!