Insert Row with Command Button Include Formulas and Merged Cells

TileGal · #1 08-29-2017, 12:21 PM

NoSparks, do you mind if I ask for your help one more time on this code?

I researched the issue of clearing the contents of the cells containing values only, and found code that works. The only problem is that my code clears the contents of the new row AND the source row. What change do I make to my code so that it clears the contents of ONLY the new row?

Code:

Private Sub CommandButton1_Click()
     Dim rowNum As Integer
    On Error Resume Next
    rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                    Title:="Insert Quote Row", Type:=1)
    If rowNum = 0 Then Exit Sub
    Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown
    
    Rows(rowNum + 1).Copy
    
    Rows(rowNum).PasteSpecial Paste:=xlPasteFormats
    Cells(rowNum + 1, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
    Rows(rowNum).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cannot get macro button to sort with merged cells on sheet	lonniepoet	Excel Programming	3	02-04-2016 04:33 PM
Create a command button that will insert another drop down menu	Tinamation	Word VBA	1	11-06-2015 03:11 PM
Table will not allow sorting because "cells are merged". I can't find the merged cells.	wendyloooo	Word Tables	1	05-26-2015 01:19 PM
Accidentally merged cells?	Fraser	Excel	2	02-16-2014 10:15 PM
Command Button	cksm4	Word VBA	7	02-27-2011 08:47 PM