clipboard cannot be emptied
 

The following macro is my crude solution (I sense there may be a much
more efficient way to do this) to a need in a multi-line file to insert
99 copies of each line between the existing lines; so a 10 line file
becomes a 1000 line file, etc. The macro works on the 1000 line
example, but when I tried it on a 20,000 line test file I got an error
that the 'clipboard cannot be emptied', debug revealed it was on the
line with stars below that the error was occuring. How do I deal with
that? Or, alternatively, is there a much better way to achieve the same
results?

Thanks in advance,

Rob

Sub Interpolate_NIRS()
numlines = Range("A1").SpecialCells(xlCellTypeLastCell).Row
MsgBox numlines
Application.ScreenUpdating = False
For j = 1 To numlines
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
For k = 1 To 99
ActiveCell.Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Insert Shift:=xlDown
**** Next
ActiveCell.Offset(1, 0).Range("A1").Select
Next
Application.ScreenUpdating = True
numlines = Range("A1").SpecialCells(xlCellTypeLastCell).Row
MsgBox numlines
End Sub


--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=380048

clipboard cannot be emptied
 

Hi Rob,

rroach wrote:
The following macro is my crude solution (I sense there may be a much
more efficient way to do this) to a need in a multi-line file to
insert 99 copies of each line between the existing lines; so a 10
line file becomes a 1000 line file, etc. The macro works on the 1000
line example, but when I tried it on a 20,000 line test file I got an
error that the 'clipboard cannot be emptied', debug revealed it was
on the line with stars below that the error was occuring. How do I
deal with that? Or, alternatively, is there a much better way to
achieve the same results?

There are a few issues with the way you did it:

Sub Interpolate_NIRS()
numlines = Range("A1").SpecialCells(xlCellTypeLastCell).Row

First of all, none of your variables are declared. I would suggest using
Option Explicit at the top of your modules, which will force you to declare
your variables before using them. If you want to do this all the time, you
can check the box in Tools | Options (Editor tab), "Require Variable
Declaration". This will help you avoid spelling errors, type mismatches,
etc.

MsgBox numlines
Application.ScreenUpdating = False
For j = 1 To numlines
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy

I don't know what this copy is doing, as you do a copy in the "k" loop
below. Plus, in most cases, there is no need to Select or Activate objects,
as you can typically work directly with the object itself. For example, if
you really wanted to copy row 1 like you did above, you could do it like
this:

ActiveCell.EntireRow.Copy

For k = 1 To 99
ActiveCell.Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Insert Shift:=xlDown
**** Next

Here, you're copying the rows one by one. This will be slower than copying
it once and doing a "bulk" insert. You can do this by referencing multiple
rows when you do your insert (see my revised code below).

ActiveCell.Offset(1, 0).Range("A1").Select
Next
Application.ScreenUpdating = True
numlines = Range("A1").SpecialCells(xlCellTypeLastCell).Row
MsgBox numlines
End Sub


I would suggest something like this:

Sub Interpolate_NIRS_2()
Dim lNumLines As Long
Dim lRow As Long

lNumLines = Range("A1").End(xlDown).Row
Application.ScreenUpdating = False

For lRow = lNumLines To 1 Step -1
Cells(lRow, 1).EntireRow.Copy
Range(Cells(lRow, 1), Cells(lRow + 98, 1)).EntireRow. _
Insert Shift:=xlDown
Next lRow

Application.ScreenUpdating = True
End Sub


You'll notice that I loop through the rows backward so I don't have to worry
about changing row numbers after inserts. Hope this helps!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

clipboard cannot be emptied
 

Jake,

Thanks for taking the time to teach me a little bit. Your code is many
times faster, and I even understand what it is doing!

Thanks again,

Rob


--
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
View this thread: http://www.excelforum.com/showthread...hreadid=380048

clipboard cannot be emptied
 

rroach wrote:
Thanks for taking the time to teach me a little bit. Your code is many
times faster, and I even understand what it is doing!

No problem, Rob - glad to help out!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]