Delete Columns in Excel using Access VBA

[gasmaskman]

Using VBA in Access, I am trying to open an excel file, detect all the columns in sheet1 that have no data in rows 2 through to the end of the file (headers in row 1), and delete these entire columns. Right now when I run the code I receive an error: "run-time error '1004': method 'Cells' of object '_global' failed" on the line of code that starts with, "If wf.CountA". Thank you in advance for any help.

Private Sub cmdDelete_Click()
    Dim xl As New Excel.Application
    Dim wf As Excel.WorksheetFunction
    Dim wk As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim strPath As String
    Dim j As Long
     
    strPath = "C:\" & SVCnumber1 & "\" & SVCnumber1 & " Output" & ".xls"
    Set wk = xl.Workbooks.Open(strPath)
    Set ws = wk.Sheets("sheet1")
    Set wf = xl.WorksheetFunction
    
    For j = 40 To 1 Step -1
        If wf.CountA(ws.Range(Cells(2, j), Cells(Rows.Count, j))) = 0 Then
            ws.Columns(j).Delete
        End If
    Next j
     
    wk.Save
    wk.Close
    xl.Quit
    Set xl = Nothing
End Sub

[gasmaskman]

I finally got the code to work! I'm posting the code just in case anyone else runs into a similar problem.

    Dim xl As New Excel.Application
    Dim wf As Excel.WorksheetFunction
    Dim wk As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim strPath As String
    Dim j As Long
     
    strPath = "C:\" & SVCnumber1 & "\" & SVCnumber1 & " Output" & ".xls"
    Set wk = xl.Workbooks.Open(strPath)
    Set ws = wk.Sheets("sheet1")
    Set wf = xl.WorksheetFunction
    
    For j = 40 To 1 Step -1
        If wf.CountA(ws.Range(ws.Cells(2, j), ws.Cells(ws.Rows.Count, j))) = 0 Then
            ws.Columns(j).Delete
        End If
    Next j
     
    wk.Save
    wk.Close
    xl.Quit
    Set xl = Nothing