none
Delete Excel column with vbscript

    Question

  • I cannot find the vbscript code to delete a column in an Excel spreadsheet. I have recorded a macro and copied that code but it won't run.
    Here is the code section I have tried:
    set NewWorkBook = objExcel.workbooks.add()
    With NewWorkBook
      .Title = "J Citizen"
      .Sheets("Sheet1").Select
      .ActiveSheet.paste ' Works to here
      .Columns("E:E").Select
      .Selection.Delete Shift =xlToLeft
    end with
    Tuesday, October 27, 2009 4:00 PM

Answers

  • The presence of objExcel suggests that this is a VBScript implementation as opposed to one in Excel's internal VBA.  If that's the case, you cannot use a named argument (which is rendered here missing a colon) AND the xl constant needs to be explicitly defined in your script.

    Try this ...

    Const xlToLeft = -4131 ' (&HFFFFEFDD)
    set NewWorkBook = objExcel.workbooks.add()
    With NewWorkBook
      .Title = "J Citizen"
      .Sheets("Sheet1").Select
      .ActiveSheet.paste ' Works to here
      .Columns("E:E").Select
      .Selection.Delete xlToLeft
    end with
    Tom Lavedas
    Tuesday, October 27, 2009 4:31 PM

All replies

  • The presence of objExcel suggests that this is a VBScript implementation as opposed to one in Excel's internal VBA.  If that's the case, you cannot use a named argument (which is rendered here missing a colon) AND the xl constant needs to be explicitly defined in your script.

    Try this ...

    Const xlToLeft = -4131 ' (&HFFFFEFDD)
    set NewWorkBook = objExcel.workbooks.add()
    With NewWorkBook
      .Title = "J Citizen"
      .Sheets("Sheet1").Select
      .ActiveSheet.paste ' Works to here
      .Columns("E:E").Select
      .Selection.Delete xlToLeft
    end with
    Tom Lavedas
    Tuesday, October 27, 2009 4:31 PM
  • Hi Tom
    Can't see my reply here but I did post one, at least I thought so.
    Thanks for your help.
    To get it to work, I had to do the following:

    Const xltoLeft = -4131
    Set NewWorkBook = objExcel.workbooks.add()
    With objExcel
      .Sheets("Sheet1").Select
      .ActiveSheet.paste
      .columns("E:E").select
      .selection.delete xltoLeft
     End With

    With NewWorkBook
      .title = "J Citizen"
      .SaveAs("C:\Documents and Settings\John\My Documents\temp\Test\"J Citizen.xls")
    End With

    I can't really understand it but done your way I got error messages like "Columns not supported" or "Title not supported"
    Looks like I need more in-depth understanding of VbScript "under the hood"
    Regards
    John Miller
    Thursday, October 29, 2009 1:10 PM
  • I have one question.

    I can delete columns and rows individually.

    But I want to delete 10 rows and 5 columns as-

    A2:D10

    Is this possible to delete using VB script ..

    Please help


    Regards, Govind

    Tuesday, September 25, 2012 10:10 AM
  • I have one question.

    I can delete columns and rows individually.

    But I want to delete 10 rows and 5 columns as-

    A2:D10

    Is this possible to delete using VB script ..

    Please help


    Regards, Govind


    This thread is closed.  Please start a new one for your question.

    Grant Ward, a.k.a. Bigteddy

    Tuesday, September 25, 2012 10:21 AM