Delete Rows from Excel 'sometimes' works?

[Runawaygeek]

Hi all,

I have a function that works sometimes, but other times gives me an error.

"Run-time error '1004
Method 'Rows' of object'_Global' Failed"

My function, opens Excel and then erases all the rows that meet a string value in a cell.

when is does work, it has this habit of making a hand full of cells set to their Max height, but thats a minor issues. I would love to solve the 1004 error.

My code is:

Private Function WipeExcel()

Dim FN As String
Dim LN As String
Dim UN As String

FN = "VOD Ops "
LN = " Schedule.xlsx"

If IsNull(Me.Combo1) = True Then MsgBox "Choose a User", vbOKOnly + vbInformation, "No Selection": Exit Function

UN = Me.Combo1

Path = FN & UN & LN

Dim filepath_tracker As String

filepath_tracker = "C:\VOD Operations\" & Path 'This is to be the Tracker of the DPN VOD OP

Dim WB As Excel.Workbook
Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set WB = xlApp.Workbooks.Open(filepath_tracker, True, False)

Dim RD As Long

For RD = Range("AL" & Rows.Count).End(-4162).Row To 2 Step -1

If Range("AL" & RD).Value = "pulled" Or Range("AL" & RD).Value = "Delivered" Then
WB.Sheets(2).Rows(RD).EntireRow.Delete
End If
Next RD
    
End Function

 

[marlan]

What does Rang refer to? Your code is running are in Access, you should have it refer to the worksheet Object.

Here is a link to an example of editing Excel from Access

 

[Runawaygeek]

What does Rang refer to? Your code is running are in Access, you should have it refer to the worksheet Object.

Here is a link to an example of editing Excel from Access

Hi

I am a little confused, I do set the object,

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

The RANGE is looking at the whole of column "LA" and then deleting Rows where the string test is true.

Thanks for the link, ill look through it,

 

[Frothingslosh]

What you've set in the above code is an application object, not a worksheet object. A worksheet object would be this (worksheet object creation is green):

Public Function Demo(ByVal WorkbookPath As String, _
                     ByVal WorksheetName As String, _
                     ByVal RowToDelete As Long)

Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet

    'Open the workbook.
    Set wkb = Excel.Workbooks.Open(WorkbookPath)
[COLOR="Green"]    'Open the worksheet.
    Set wks = wkb.Sheets(WorksheetName)[/COLOR]
    'Delete the selected row.
    wks.Rows(RowToDelete).EntireRow.Delete
    
End Function

Please note that the above is air code - it compiles, but I haven't actually run it. Basically, you create a worksheet object and execute your actions on that rather than repeatedly designating a sheet in the sheets collection of a workbook object.

Marlan's link is a tiny bit technical but really should help.

 

[JHB]

For RD = Range("AL" & Rows.Count).End(-4162).Row To 2 Step -1

The code you have in the above line expects that Range of Row is known objects in MS-Access which they are not.
You're missing the reference to the Excel application/worksheet object.

For RD = [B][COLOR=Red]MissingReference.[/COLOR][/B]Range("AL" & [B][COLOR=Red]MissingReference.[/COLOR][/B]Rows.Count).End(-4162).Row To 2 Step -1

And there are more missing references in your code.

I suspect your code runs each second time.

 

[Runawaygeek]

Thanks guys,

having read through your advice, i have changed my SET to now have the worksheet and the sheet name etc.. this has improved things.

What i am unsure about is how to correct the reference line,

For RD = MissingReference.Range("AL" & MissingReference.Rows.Count).End(-4162).Row To 2 Step -1

any pointers?

Thanks again,

 

[JHB]

MissingReference should be replaced variable name you use for the Excel application or worksheet object.
Example:

For RD = [B][COLOR=Red]xlApp.[/COLOR][/B]Range("AL" & [B][COLOR=Red]xlApp.[/COLOR][/B]Rows.Count).End(-4162).Row To 2 Step -1

 

[Runawaygeek]

***JUST SEEN A REPLY** so Edited

Ok, i did a bit more reading and I now have this:

Private Function WipeExcel_Optional()

Dim FN As String
Dim LN As String
Dim UN As String
FN = "VOD Ops "
LN = " Schedule.xlsx"
If IsNull(Me.Combo1) = True Then MsgBox "Choose a User", vbOKOnly + vbInformation, "No Selection": Exit Function
UN = Me.Combo1
Path = FN & UN & LN
Dim filepath_tracker As String
filepath_tracker = "\\DNE\collaborate\MTP&O\VOD Operations\VOD Operations\" & Path 'This is to be the Tracker of the DPN VOD OP

Dim xlApp As Excel.Application
Dim WKB As Excel.Workbook
Dim WKS As Excel.Worksheet


Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open (filepath_tracker)
Set WKB = xlApp.ActiveWorkbook
Set WKS = WKB.Sheets("Tracking")
xlApp.Visible = True


For RD = xlApp.Range("AL" & Rows.Count).End(xlUp).Row To 2 Step -1

If xlApp.Range("AL" & RD).Value = "pulled" Or xlApp.Range("AL" & RD).Value = "Delivered" Then
WKB.Sheets(WKS).Rows(RD).EntireRow.Delete
End If
    Next RD
    
Set WKS = Nothing
Set WKB = Nothing
Set xlApp = Nothing

End Function

I still sometimes get the error, but i also get a Subscript out of range error as well.. :-S

 

[Runawaygeek]

MissingReference should be replaced variable name you use for the Excel application or worksheet object.
Example:

For RD = [B][COLOR=Red]xlApp.[/COLOR][/B]Range("AL" & [B][COLOR=Red]xlApp.[/COLOR][/B]Rows.Count).End(-4162).Row To 2 Step -1

Why (-4162) and not XLUP?

Thanks,

 

[Cronk]

Is that a rhetorical question? You were the one who posted that in your first post.

 

[marlan]

Thanks guys,

having read through your advice, i have changed my SET to now have the worksheet and the sheet name etc.. this has improved things.

What i am unsure about is how to correct the reference line,

For RD = MissingReference.Range("AL" & MissingReference.Rows.Count).End(-4162).Row To 2 Step -1

any pointers?

Thanks again,

As far as I understand, the missing reference is a Worksheet object. I your case:

For RD = WKB.Range("AL" & WKB.Rows.Count).End(-4162).Row To 2 Step -1

(As for (-4162), It is a value from you code... )

 

[JHB]

...I still sometimes get the error, but i also get a Subscript out of range error as well.. :-S

Maybe it would help if you put in the missing reference in all places in the code line I showed you!

 

[Runawaygeek]

Is that a rhetorical question? You were the one who posted that in your first post.

er.. yer, I have got that from another forum and if i am utterly honest, have no idea what the numbers do. The XLUP makes sense, but the numbers not so much!

 

[Runawaygeek]

Maybe it would help if you put in the missing reference in all places in the code line I showed you!

What a spanner I am! Wood for trees I tell you!

I have added in the missing code,

For RD = Wks.Range("AL" & Wks.Rows.Count).End(-4162).Row To 2 Step -1

It seems to be working well now.
thanks for your help!

 

[marlan]

Glad I could help

Mow make sure you use the correct object in the entire code (Access VBA has other assumptions than Excel VBA, when referring to Excel from out of Excel you must always use these App->Workbook->Worksheet objects).

ATB