Results 1 to 11 of 11

Thread: Cutting and pasting columns in an Excel spreadsheet.

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2011
    Posts
    32

    Cutting and pasting columns in an Excel spreadsheet.

    Hello,

    What should I write after .PasteSpecial?

    Code:
    xlSheet.Range("B:B").Cut()
    With xlSheet.Range("A:A")
        .Select()
        .PasteSpecial
    End With
    Thanks.
    Last edited by dday9; Mar 31st, 2015 at 01:41 PM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,697

    Re: Cutting and pasting columns in an Excel spreadsheet.

    Is this VBA or VB.Net? Also, please use code tags to format your code:
    [CODE]'Hello World[/CODE]
    I've edited your original post.

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2011
    Posts
    32

    Re: Cutting and pasting columns in an Excel spreadsheet.

    Thanks.

    This is VB.Net, not VBA.

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,697

    Re: Cutting and pasting columns in an Excel spreadsheet.

    Here is the documentation on the Range.PasteSpecial. Fill the appropriate parameters with what you're trying to accomplish.

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2011
    Posts
    32

    Re: Cutting and pasting columns in an Excel spreadsheet.

    Getting an error msg:
    “COMException was unhandled. PasteSpecial method of range class failed”.
    The error is pointing to the line in code with the .PasteSpecial

    Code:
    Imports Microsoft.Office.Interop
    Code:
    xlSheet.Range("B:B").Cut()
            With xlSheet.Range("A:A")
                .Select()
                .PasteSpecial(Excel.XlPasteType.xlPasteAll)
            End With

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,697

    Re: Cutting and pasting columns in an Excel spreadsheet.

    You did not specify all of the parameters, did the code even compile?

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2011
    Posts
    32

    Re: Cutting and pasting columns in an Excel spreadsheet.

    The other parameters are all optional.
    The code compiled fine.

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,697

    Re: Cutting and pasting columns in an Excel spreadsheet.

    I took a look at the documentation and found the following:
    PasteType: Microsoft.Office.Interop.Excel.XlPasteType

    Optional XlPasteType. The part of the range to be pasted. Can be one of the following XlPasteType constants:
    xlPasteAll default
    xlPasteAllExceptBorders
    xlPasteColumnWidths
    xlPasteComments
    xlPasteFormats
    xlPasteFormulas
    xlPasteFormulasAndNumberFormats
    xlPasteValidation
    xlPasteValues
    xlPasteValuesAndNumberFormats
    Try leaving the parameter empty and see if it will work as it's the default value.

  9. #9

    Thread Starter
    Member
    Join Date
    Apr 2011
    Posts
    32

    Re: Cutting and pasting columns in an Excel spreadsheet.

    The parameter cannot be left empty.

  10. #10
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,697

    Re: Cutting and pasting columns in an Excel spreadsheet.

    The only thing that I could gather from your error is that the range is not setup correctly, but I don't use the Excel interop often so I'm unsure. I'd suggest somebody else stepping in to see what could be causing this.

  11. #11

    Thread Starter
    Member
    Join Date
    Apr 2011
    Posts
    32

    Re: Cutting and pasting columns in an Excel spreadsheet.

    Problem solved.

    This is working:

    Code:
    xlSheet.Range("B:B").Cut(xlSheet.Range("A:A"))
    Thanks for trying to help. Have a nice day.

Posting Permissions

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



Click Here to Expand Forum to Full Width