Insert Row with Command Button Include Formulas and Merged Cells

NoSparks · #1 08-29-2017, 01:24 PM

I actually don't know why that would be clearing both rows as it's only being applied to the original row (rowNum+1)
although it is prior to pasting the contents even tho it's already copied.

I'd relocate the instruction and only apply it to the new row.

Code:

Private Sub CommandButton1_Click()
     Dim rowNum As Integer
     
'don't let Excel tell me about errors
'On Error Resume Next

'get rowNum to deal with
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                Title:="Insert Quote Row", Type:=1)

'incase user closes, cancels or doesn't enter a row number
If rowNum = 0 Then Exit Sub

'prevent screen flash
Application.ScreenUpdating = False

'insert new row
Rows(rowNum).Insert Shift:=xlDown

'copy original row
Rows(rowNum + 1).Copy

'paste formulas of original row
Rows(rowNum).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False

'paste formatting of original row
Rows(rowNum).PasteSpecial Paste:=xlPasteFormats

'clear cells with constants in newly inserted row
Cells(rowNum, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

'stop the marching ants
Application.CutCopyMode = False

'remove selection from entire new row
Range("A" & rowNum).Select

'turn screen updating back on
Application.ScreenUpdating = True

End Sub

Hope that helps.

TileGal · #2 08-29-2017, 02:00 PM

Works like a charm! And much faster than the other solution I had.

I only had to UNCOMMENT the error code.

Thanks again! You're fabulous!

Quote:

Originally Posted by NoSparks

I actually don't know why that would be clearing both rows as it's only being applied to the original row (rowNum+1)
although it is prior to pasting the contents even tho it's already copied.

I'd relocate the instruction and only apply it to the new row.

Code:

Private Sub CommandButton1_Click()
     Dim rowNum As Integer
     
'don't let Excel tell me about errors
'On Error Resume Next

'get rowNum to deal with
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
                                Title:="Insert Quote Row", Type:=1)

'incase user closes, cancels or doesn't enter a row number
If rowNum = 0 Then Exit Sub

'prevent screen flash
Application.ScreenUpdating = False

'insert new row
Rows(rowNum).Insert Shift:=xlDown

'copy original row
Rows(rowNum + 1).Copy

'paste formulas of original row
Rows(rowNum).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False

'paste formatting of original row
Rows(rowNum).PasteSpecial Paste:=xlPasteFormats

'clear cells with constants in newly inserted row
Cells(rowNum, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

'stop the marching ants
Application.CutCopyMode = False

'remove selection from entire new row
Range("A" & rowNum).Select

'turn screen updating back on
Application.ScreenUpdating = True

End Sub

Hope that helps.

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