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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
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
771
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,102,784
Messages
5,488,860
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top