I am attempting to use the code below in access vba and am struggling greatly. Any help is greatly appreciated.
Code:
Sub deleterows()
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
For j = Range("A1").End(xlToRight).Column To 1 Step -1
If wf.CountA(Range(Cells(2, j), Cells(Rows.Count, j))) = 0 Then
Columns(j).Delete
End If
Next j
End Sub
I thought it was easier to show the excel vba code above, but this is the code that I have so far for access vba.
Code:
Dim CurrentFolder As String
Dim CurrentCycle As String
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim strPath As String
Dim ws As Excel.worksheet
Dim j As Integer
CurrentCycle = Format(Date, "yyyymm")
CurrentFolder = "C:\" & SVCnumber1 & "\"
strPath = "C:\" & SVCnumber1 & "\" & SVCnumber1 & " Output" & ".xls"
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
For j = Range("A1").End(xlToRight).Column To 1 Step -1
If Excel.Application.WorksheetFunction.CountA(ApXL.Sheets("sheet1").Range(Cells(2, j), Cells(Rows.Count, j))) = 0 Then
Columns(j).Delete
End If
Next j
The part of the code that I am struggling with is trying to loop through each column of sheet1 to detect if rows 2 through the last row have no data in them.
I receive the error "Run-time error '1004': Application-defined or object-defined error" on the line of code that starts with 'If Execel.Application'...
OK, one possibility, you create an Excel.Application object here . . .
Code:
Set ApXL = CreateObject("Excel.Application")
. . . and your code references an Excel.Application here . . .
Code:
If [COLOR="Red"]Excel[/COLOR].Application.WorksheetFunction.CountA(ApXL.Sheets("sh . . .
. . . but this object called "Excel" is not declared anywhere. Maybe your code should be . . .
Code:
If [COLOR="Red"]ApXL[/COLOR].Application.Workshee . . .
Also, if this is the problem, you should research how to "Require Variable Declaration" in VBA code settings.
Also, notice what information you needed to provide for someone to be able to find the problem. Reading other people's code is hard.
hth
Thanks for helping me out lagbolt. With your help, I tweaked the code around and can now successfully run the code on the first try, but when I run the code a second time I receive the error "run-time error '1004':
method 'range' of object '_global' failed". The run time error falls on the "For j = Range(" line of code.
I noticed that Excel*32 remains in the task manager even though it appears as though no excel file is open. If I terminate Excel*32 after each run of code then everything works fine.
Code:
Dim CurrentFolder As String
Dim ApXL As Object
Dim xlWBk As Object
Dim strPath As String
Dim j As Long
CurrentFolder = "C:\" & SVCnumber1 & "\"
strPath = "C:\" & SVCnumber1 & "\" & SVCnumber1 & " Output" & ".xls"
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
For j = Range("A1").End(xlToRight).Column To 1 Step -1
If ApXL.WorksheetFunction.CountA(Workbooks(SVCnumber1 & " Output" & ".xls").Sheets("sheet1").Range(Cells(2, j), Cells(Rows.Count, j))) = 0 Then
Columns(j).Delete
End If
Next j
xlWBk.Save
xlWBk.Close
Set xlWBk = Nothing
ApXL.Quit
Set ApXL = Nothing
Now, are you running this code in Access? This should never work, since Range() is unknown in Access, and I don't see where you declare it or assign it a value . . .
Code:
For j = [COLOR="Red"]Range[/COLOR]("A1").End(xlToRight).Column To 1 Step -1
Possibly you did not "require variable declaration?" If not, go to the top of your code module, and make sure you see these two lines
Code:
Option Compare Database
Option Explicit [COLOR="Green"]'add this if it's not present[/COLOR]
Compile your code, or try to run it, and see if you get errors you didn't get previously. Those errors need to be resolved.
A little background: Access, by default, will automatically create a variable from any unknown identifier that hasn't been declared, and this is bad because you can declare this . . .
Code:
Dim MyVariable As String
MyVariable = "This is a test!!!"
Debug.Print MyVaraible
. . . but maybe you didn't notice that I misspelled variable in the last line, so the result will never print out. Access will create the variable automatically, will print nothing, correctly, and the process will fail, without any error message. This is very hard to find.
So before we troubleshoot anymore, please confirm that Option Explicit is set. To set it globally for new module, go to CodeWindowMainMenu-->Tools-->Options-->Editor Tab-->Code Setting Block-->Require Variable Declaration, is the second checkbox in the list. Make sure it is set.
I guess my difficulty is figuring out what is considered a variable that needs declaration compared to code that doesn't need declaration. I thought Range, like CountA, doesn't require a declaration.
I declared the Range variable and added a worksheet variable (ws) to prevent the same error from occuring on the next line of code. I also changed the 'For j = Range' code so that I could isolate any more errors in the code. At this point, the only part of the code causing is the error "run-time error '1004': method 'Cells' of object '_global' failed" on the line of code "If AppXL.WorksheetFunction". I'm not sure what to declare 'Cells' as, I've tried declaring it as a range, object and string, but none have worked. Thanks again for any help.
Updated code:
Code:
Dim CurrentFolder As String
Dim ApXL As Object
Dim xlWBk As Object
Dim strPath As String
Dim j As Long
Dim ws as Excel.worksheet
Dim Range as Range
CurrentFolder = "C:\" & SVCnumber1 & "\"
strPath = "C:\" & SVCnumber1 & "\" & SVCnumber1 & " Output" & ".xls"
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
Set ws = xlWBk.Sheets("sheet1")
For j = 40 To 1 Step -1
If ApXL.WorksheetFunction.CountA(Workbooks(SVCnumber1 & " Output" & ".xls").Sheets("sheet1").Range(Cells(2, j), Cells(Rows.Count, j))) = 0 Then
ws.Columns(j).Delete
End If
Next j
xlWBk.Save
xlWBk.Close
Set ws = Nothing
Set xlWBk = Nothing
ApXL.Quit
Set ApXL = Nothing
Private Sub Testq298r6749123647()
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
. . . and now. What are you trying to do? Specifically. Delete empty columns?
I'm receiving a "run-time error '1004': method 'Cells' of object '_global' failed" error on the line of code that starts with, "If wf.CountA". Interestingly, in your code, the 'Range' object isn't causing the problem anymore, while, before you cleaned up my code, the 'Range' object gave an error until I declared it.
I finally got the code to work! I'm posting the code just in case anyone else runs into a similar problem.
Code:
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
