Insert Row with Command Button Include Formulas and Merged Cells

TileGal · #1 08-29-2017, 08:51 AM

I am very new to VBA, and I understand coding. I need some help on a VBA code in Excel 2013.

A portion of my spreadsheet includes rows for quoting items. My end users are not very Excel savvy, so I have researched the code and cobbled together enough to create and use a button that will insert a row in the row number of their choice and include the formulas of the previous row (see below).

Where I'm getting stuck is keeping two sets of cells merged in the new row. Cells D(rowNum) through G(rowNum) should be merged, and H(rowNum) through J(rowNum). I've done lots of research on VBA code to insert a row and keep the merged cells, but I cannot figure out how to integrate it into my existing code. (This is where my lack of basic VBA rules knowledge comes in.)

I would really appreciate any help you can give, and if there's any additional information I need to provide, please let me know.

Thanks!!

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)
Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown

Rows(rowNum + 1).Copy

Rows(rowNum).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

NoSparks · #2 08-29-2017, 10:19 AM

Add a second paste line

Code:

Rows(rowNum).PasteSpecial Paste:=xlFormats

You might also want to add

Code:

If rowNum = 0 Then Exit Sub

right after the InputBox.

TileGal · #3 08-29-2017, 10:59 AM

Thanks for the fast reply, NoSparks!

Using the Paste Special Formats code is actually one of the first things I tried (and researched). What I've learned is that while you can copy and paste formats on the user end of Excel and maintain merged cells, that doesn't seem to work in VBA.

(I did try it again with your exact code just in case I was wrong, and sadly it did not work.)

Thank you for the 'If 0 Then End' code though! I did add that and that does work.

If you have any other suggestions about keeping the merged cells, I'd love to see them and give them a try!

Quote:

Originally Posted by NoSparks

Add a second paste line

Code:

Rows(rowNum).PasteSpecial Paste:=xlFormats

You might also want to add

Code:

If rowNum = 0 Then Exit Sub

right after the InputBox.

NoSparks · #4 08-29-2017, 11:28 AM

That worked on what I setup to test with otherwise I wouldn't have suggested it.
It's unlikely I setup every thing the way you have it.
If you attach a sample workbook where this doesn't work and I'll check out what's happening.

If you remove

Code:

On Error Resume Next

does Excel indicate any errors ?

TileGal · #5 08-29-2017, 11:33 AM

My apologies!! You are correct!

I forgot the first rule of working on a project someone else designed: MAKE SURE THEY DID EVERYTHING RIGHT!

The row I was using as my "test case" did not have the cells merged properly.

Problem solved! The Paste Special Formats code works!

And now that I've gotten deeper into user testing, I've come across another challenge. Maybe you can help me with that too???

The insert is copying and pasting the values. Ideally, the new row would be blank OTHER THAN THE FORMULAS (hence the c/p formulas code).

I'm just starting to research this one, so I may just stumble across the answer myself (in between every other fire I have to put out), but there's nothing like picking the brain of someone with experience when opportunity affords.

Thanks again!!!

Quote:

Originally Posted by NoSparks

That worked on what I setup to test with otherwise I wouldn't have suggested it.
It's unlikely I setup every thing the way you have it.
If you attach a sample workbook where this doesn't work and I'll check out what's happening.

If you remove

Code:

On Error Resume Next

does Excel indicate any errors ?

NoSparks · #6 08-29-2017, 12:00 PM

Glad you got things sorted out.

TileGal · #7 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

NoSparks · #8 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 · #9 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.

NoSparks · #10 08-29-2017, 02:20 PM

Quote:

I only had to UNCOMMENT the error code.

Why? It would be better to fix the error or handle it differently, what is Excel telling you is wrong?

TileGal · #11 08-29-2017, 02:22 PM

Run-time error '1004':
No cells were found.

Highlighting:

Code:

 Cells(rowNum, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

Quote:

Originally Posted by NoSparks

Why? It would be better to fix the error or handle it differently, what is Excel telling you is wrong?

NoSparks · #12 08-29-2017, 02:55 PM

I'm not able to reproduce that error so I guess if nothing is getting screwed up because of it then leave well enough alone.

Good luck.

TileGal · #13 08-30-2017, 06:15 AM

Thanks again for all your help!

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