Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: vba delete column

    Hi VBA gurus,

    I need to write a function in Access vba to delete entire column or columns and shift lef in Excel. After delete the column(s), it will save the change. This function can be reused so it will allow me to pass parameters - Excel file name, sheet name, and column number(column ranges).

    I tried but my code does not work. Any help will be greatly appreciated ! Thank you very much!

    I use a command button to invoke the function but
    I got "Run-time error '1004'; Application-defined or object-defined error" on the line oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft

    Private Sub cmd1_Click()
    Dim bookname As String
    bookname = "c:\file1.xls"

    Dim sheetname As String
    sheetname = "file1"

    Dim cellcolumn As String

    cellcolumn = "H2"

    Call DeleteColumn(bookname, sheetname, cellcolumn)


    End Sub

    Public Sub DeleteColumn( _
    ByVal bookname As String, _
    ByVal sheetname As String, _
    ByVal cellcolumn As String)

    Const xlShiftToLeft = -4159

    Dim oXL As Object
    Dim oBook As Object
    Dim oSheet As Object

    Set oXL = CreateObject("Excel.Application")
    Set oBook = oXL.Workbooks.Open(bookname)
    Set oSheet = oBook.Worksheets(sheetname)

    oSheet.Range(cellcolumn).EntireColumn.Delete xlShiftToLeft
    Set oSheet = Nothing

    oBook.Close True
    Set oBook = Nothing

    oXL.Quit
    Set oXL = Nothing

    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Perhaps

    oSheet.Columns(cellcolumn).Delete Shift:=xlShiftToLeft

    This pre-supposes that 'cellcolumn' is a valid coulmn reference string

    ie "F:F" or "F" or "C:F" etc

    It could also be an integer for a single column.


    MTB

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MikeTheBike View Post
    Hi

    Perhaps

    oSheet.Columns(cellcolumn).Delete Shift:=xlShiftToLeft

    This pre-supposes that 'cellcolumn' is a valid coulmn reference string

    ie "F:F" or "F" or "C:F" etc

    It could also be an integer for a single column.


    MTB
    Hi MTB,

    Thank you so much again!!

    I changed the code in the command button to either

    Dim cellcolumn As String

    cellcolumn = "H"

    or

    Dim cellcolumn As Integer

    cellcolumn = 8

    but I still get the same error on the same line.
    It seems to me that it may not be the input parameters, but the reference of excel sheet????

    By the way, I have very limited knowledge about excel. I don't know about F:F or C:F. Is it supposed to have a number to reference the row number when we reference a cell like A2 ?
    Would you please teach me more about that and maybe use some examples?

    Thank you so much!
    Last edited by newguyinaccess; 11-09-10 at 09:12.

Posting Permissions

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