"the object invoked has disconnected from its clients" excel 2010

[marish86]

Hi, I'm getting the following error message when running a VBA macro in excel 2010,

"Run-time error '-2147417848 (80010102)':
Automation error
The object invoked has disconnected form its clients. "

The issue is that on excel 2007 the macro works perfectly, however I've tried to run the same macro in two different computers that have excel 2010 and I can't get the macro to work. On one of the computers I get a different error related to "not enough resources" (even though nothing is running on the background)

On both computers the macro stops in the same point:

 Cells.Select
    Selection.Sort Key1:=Range("A" & iRow), Order1:=xlAscending, header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

The macro is very complex and this is just part of it. I've seen that in some cases the solution is to add "Option Explicit" at the top of the module but it is already there.

 

[Andrew Poulsom]

Why are you selecting all the cells on the worksheet? In fact why are you selecting at all?

 

[marish86]

Thanks!
In a first macro data is imported from a text file, then it calls this other macro to delete unnecessary data. Please see below the whole code,

Sub deleteUnneededData()
    Dim iRow As Integer
    ThisWorkbook.Sheets("Source").Select
        
    iRow = 2
    
    Cells.Select
    Selection.Sort Key1:=Range("A" & iRow), Order1:=xlAscending, header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Do Until Not IsDate(Range("A" & iRow).Value)
        If DateDiff("d", Date, Range("a" & iRow).Value) < -7 Or _
                    DateDiff("d", Date, Range("a" & iRow).Value) > CInt(frmInitialize.lstDaysAhead.Value) Then
            Range("a" & iRow).Select
            Selection.EntireRow.Delete
        Else
            iRow = iRow + 1
        End If
        
        Application.StatusBar = "Looking for unneeded rows on row " & iRow
    Loop
    
    iRow = 2
    Range("A" & iRow).Select
    Do Until Not IsDate(Range("A" & iRow).Value)
        If IsEmpty(Range("c" & iRow).Value) Then
            Range("c" & iRow).Value = "1:00"
        End If
        iRow = iRow + 1
        Application.StatusBar = "Looking for empty estimated times (set to 1:00) on row " & iRow
    Loop
    
End Sub

If necessary I can post the first macro that imports the data, just let me know.

 

[Andrew Poulsom]

You could try using Range("A1").CurrentRegion instead of Cells.

 

[marish86]

That worked!! Thanks a lot for the help!