+ Reply to Thread
Results 1 to 4 of 4

clipboard cannot be emptied

  1. #1
    Registered User
    Join Date
    03-14-2005
    Posts
    21

    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

  2. #2
    Jake Marx
    Guest

    Re: 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. #3
    Registered User
    Join Date
    03-14-2005
    Posts
    21

    work well

    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

  4. #4
    Jake Marx
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1