Code help: Insert Row / Copy and Clear contents

dellmech

New Member
Joined
Apr 22, 2018
Messages
14
I have a pretty neat code I use to insert a row and it copies the formulas from the row below and pastes them into the new inserted row. It then clears contents of the new row. However, IF there are no contents in that row it copied, I get an error that says "No cells were found". Is there a way to modify the code below to not get this error message when the row it copies doesn't have contents? (the reason I have this code is in order to copy formulas into the new row)

Sub AddNewRow()
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert
Application.CutCopyMode = False
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
722
Code:
Sub AddNewRow()     
    ActiveCell.EntireRow.Insert Shift:=xlDown    
    With ActiveCell
        .Offset(1).EntireRow.Copy .EntireRow
        On Error Resume Next
        .EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
        On Error GoTo 0
    End With
End Sub
 
Last edited:

dellmech

New Member
Joined
Apr 22, 2018
Messages
14
Thanks but that didn't seem to work exactly right. It didn't copy anything over. I added "On Error Resume Next" to the top of my code and it seemed to resolve the issue.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
722
When I first posted my answer, I inadvertently forgot this line

Code:
[COLOR=#333333].Offset(1).EntireRow.Copy .EntireRow[/COLOR]
Which performs the copy. But no matter. Glad you got your problem sorted out.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,840
Messages
5,446,817
Members
405,417
Latest member
egrospe17

This Week's Hot Topics

Top