Insert Multiple Row Macro Bug When Cell Selected Is Cut Or Copied

[miketurn]

Kind of weird issue to explain but let me try.

I created a very elegant macro that adds a total of 5 new rows, as shown below...

Sub Add5Rows()
   Selection.EntireRow.Insert
   Selection.EntireRow.Insert
   Selection.EntireRow.Insert
   Selection.EntireRow.Insert
   Selection.EntireRow.Insert
End Sub

If I select a cell or a couple of cells and hit CTRL + C to copy, those cells get an animated dashed border around them.
Then say I don't have enough room to paste them somewhere, so I need to add some rows.
If I run my "Add5Rows" macro above while those cells are still in "copy" mode (dashed animation) it will add 4 blank rows and in the 5th row it will add that copied text value to every single column within that row.
So if the text copied was "APPLE", I get the word "APPLE" in column A B C D etc.

If I perform these steps above using CTRL + X to cut, it does something different, it pastes the value in the cell selected and adds 5 rows below it.

As I mentioned kind of a weird request because it is slightly a bug and slightly probably user error on my part for attempting this.
Not a big deal, but does anyone know of a simple fix for something like this, other than just not making the mistake of doing it?
A fix that allows me to run my macro to add 5 new rows but not paste the copied or cut value at all? Just let me perform the paste action manually myself afterwards.
This may be more of a software coded issue as opposed to something that could be fixed with a macro, but if anyone has any suggestions, please let me know.
(I am aware that I could just use something like "insert copied cells", but don't want to in certain situations)

Thank You

 

[Eric W]

Excel has some built-in rules for copying/inserting/etc. which have to do with the size of the data in the clipboard, and the selected/inserted range. You might be able to get around it by saving what's on the clipboard, clearing the clipboard, inserting the lines, then restoring the data to the clipboard. That code would look like:

Sub ClipInsertSave()
    
    Set clipboard = New dataobject
    clipboard.getfromclipboard
    mydata = clipboard.gettext
    
    clipboard.settext ""
    clipboard.putinclipboard
    
    Selection.Resize(5).EntireRow.Insert
    
    Set clipboard = New dataobject
    clipboard.settext mydata
    clipboard.putinclipboard
    
End Sub

Also, you may need to go to the VBA toolbar, select Tools, References, and check "Microsoft Forms 2.0 Object Library" to get this to run.

You'll notice that the marching ants disappear after running this, but the data will still be on the clipboard. But data only, the formatting will not be copied. So it's an imperfect solution, but it may work for you. Let us know.

 

[miketurn]

@Eric W
Thank you for your response, very clear.

First off thank you for the following code, because when creating this macro I could not seem to find out how to create multiple rows with one line of code

Selection.Resize(5).EntireRow.Insert

At first I was hesitant to give this a try after you mention that it elminates the formatting, but I tried it out and it actually works pretty great, I have tested it out a little and have not run into any issues.

(Just a quick question for you or another forum member)
I use my "Add 5 Row" tool a great deal, can you or any other users see any reason why I shouldn't replace it entirely with the macro that you provided? Do you think the extra coding would cause any problems if used as a replacement?
Would some kind of "If" statement offer some kind of benefit, meaning "if" cell selected is copied (dashed lined animation) then place item in clipboard, but "if not" just add 5 rows, or does this macro already pretty much do this automatically, because if there isn't any cells copied before, then in theory it would have nothing to put in the clipboard.

Sub ClipInsertSave()
    
    Set clipboard = New dataobject
    clipboard.getfromclipboard
    mydata = clipboard.gettext
    
    clipboard.settext ""
    clipboard.putinclipboard
    
    Selection.Resize(5).EntireRow.Insert
    
    Set clipboard = New dataobject
    clipboard.settext mydata
    clipboard.putinclipboard
    
End Sub

Again thank you for this macro it works great, it also has gotten thinking / creating a couple other tools now that I see that something like this can be done.

 

[Eric W]

If it works for you, I don't see any reason you can't use this in place of your old macro. It's not much longer, and certainly wouldn't take longer to run. And as you surmised, there's not much point in adding the "If" logic, since if you start out with an empty clipboard, you'll still end up with one at the end of the macro.

Glad this worked for you!

 

[miketurn]

Perfect, thank you again so much for all your help, this comes in handy.

 

[miketurn]

@Eric W and or anyone who may be able to tweak this macro from above a little bit.
Using your macro as a replacement for my "Add 5 Rows" tool works great as a replacement, the only thing is, is the first initial run of the macro, when the clipboard is clear, it produces the following error.....

Run-time error '-2147221404 (80040064)':
DataObject:GetText Invalid FORMATETC structure

Does anyone know of a piece of coding that could be added, to ignore that part of the macro, when the clipboard is empty?

Thank You

 

[Eric W]

What line does it fail on? You could try putting

On Error Resume Next

as the first line in the macro.

 

[miketurn]

@Eric W
Thank you for your response, that fixed it.

The "debug" highlights the following line of text in the macro....

mydata = clipboard.GetText

As mentioned the response you gave fixed it, not sure if this more specific information I am supplying you with requires a more specific fix?

Thank You

 

[Eric W]

No, that's the line I expected. I just wasn't sure if I needed to be more specific where I put the On Error line, or if I needed something a little more targeted. Glad you got it working.