Code help:  Insert Row / Copy and Clear contents
Results 1 to 4 of 4

Thread: Code help: Insert Row / Copy and Clear contents
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code help: Insert Row / Copy and Clear contents

    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 by dellmech; May 13th, 2018 at 06:34 PM.

  2. #2
    Board Regular
    Join Date
    May 2017
    Posts
    469
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code help: Insert Row / Copy and Clear contents

    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 by rlv01; May 13th, 2018 at 07:40 PM. Reason: Oops! I forgot the copy statement

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code help: Insert Row / Copy and Clear contents

    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.

  4. #4
    Board Regular
    Join Date
    May 2017
    Posts
    469
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code help: Insert Row / Copy and Clear contents

    When I first posted my answer, I inadvertently forgot this line

    Code:
    .Offset(1).EntireRow.Copy .EntireRow
    Which performs the copy. But no matter. Glad you got your problem sorted out.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •