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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top