Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where does Ms XL store the clipboard ? or where does MS Windowsstore clipboard ? | Setting up and Configuration of Excel | |||
Where does Ms XL store the clipboard ? or where does MS Windowsstore clipboard ? | Setting up and Configuration of Excel | |||
Clipboard empty but still get waring that clipboard is full | Excel Discussion (Misc queries) | |||
Clipboard | Excel Discussion (Misc queries) | |||
clipboard | Excel Programming |