Code Crashing Sometimes

[termeric]

Hello, i'm trying to figure out why my code crashes sometimes, but works othertimes, seemingly at random.

I am comparing the current roster to the last roster so i can build a list of people who has departed. i add people to the end of the departures list, adn then remove any duplicates, so someone can only be on there once. I'm removing duplicates based on column 1, which is employee ID.

My code crashes sometimes when the Departures sheet is blank, but also sometimes when it is not blank. sometimes after it crashes i click the play button to resume and it just works, and other times it just continues to crash. The error message i get is Application-defined or object-defined error.

this is the line that it debugs to

Sheets("Departures").Range(Cells(1, 1), Cells(r, c)).RemoveDuplicates Columns:=1, Header:=xlYes

What am i doing wrong here?

 Sub FindDifferences()
 'compares Roster to Last Roster to generate the Departures
 'this tab does not clear, it just builds on what was there before
 
    Dim i As Long, ii As Long, sq1 As Variant, sq2 As Variant, m As Long, r As Long, c As Long, v As Long
    

    r = Sheets("Departures").UsedRange.Rows.Count
    
    sq1 = Sheets("Roster").Cells(1).CurrentRegion.Columns(1)
    sq2 = Sheets("LastRoster").Cells(1).CurrentRegion.Columns(1)

    Sheets("LastRoster").Rows(1).Copy Sheets("Departures").Range("A1")


    For i = 1 To UBound(sq2)
        ii = 0
        On Error Resume Next
        ii = Application.Match(sq2(i, 1), sq1, 0)
        On Error GoTo 0
        If ii = 0 Then
            m = m + 1
             Sheets("LastRoster").Rows(i).Copy Sheets("Departures").Range("A" & m + r)
        End If
    Next
    
    'get the new row and col values
    r = Sheets("Departures").UsedRange.Rows.Count
    c = Sheets("Departures").UsedRange.Columns.Count
    

    Sheets("Departures").Range(Cells(1, 1), Cells(r, c)).RemoveDuplicates Columns:=1, Header:=xlYes

'Delete blank rows
    For v = 1 To r
        If Sheets("Departures").Range("A" & v).Value = "" Then Sheets("departures").Range("A" & v).EntireRow.Delete
    Next v

End Sub

 

[Joe4]

I could see you getting that message if r=0, r=1, or c=0 as you are saying that the data has a header row.
So that would mean that there is no data.

Confirm what r and c are when you get the error (hover over the "r" and "c" variables and it should tell you what their value is at that point in time).

 

[termeric]

My headings row is never deleted, so sometimes r = 1. I just cleared the actual data and its currently running fine with r =1 and c = 43. however, when i update my data first, which runs this subroutine after i import new data, its crashing again with r = 1 and c = 43. And then after i click debug, if i hit play, it finishes with out erroring again.

 

[Joe4]

My headings row is never deleted, so sometimes r = 1. I just cleared the actual data and its currently running fine with r =1 and c = 43. however, when i update my data first, which runs this subroutine after i import new data, its crashing again with r = 1 and c = 43. And then after i click debug, if i hit play, it finishes with out erroring again.

If r=1, then I WOULD expect an error.
That means that all you have is the header, and no rows of data. It cannot remove duplicates from a range with no rows of data!
If r = 1, then it means you only have a header and no rows of data!
So either your calculation of r is wrong, or you have no data.

If you sometimes have no data, then you can skip over the remove duplicates line of code by wrapping that line of code inside and IF/THEN statement this:

If r > 1 Then
    Sheets("Departures").Range(Cells(1, 1), Cells(r, c)).RemoveDuplicates Columns:=1, Header:=xlYes
End If

 

[termeric]

If r=1, then I WOULD expect an error.
That means that all you have is the header, and no rows of data. It cannot remove duplicates from a range with no rows of data!
If r = 1, then it means you only have a header and no rows of data!
So either your calculation of r is wrong, or you have no data.

If you sometimes have no data, then you can skip over the remove duplicates line of code by wrapping that line of code inside and IF/THEN statement this:

If r > 1 Then
    Sheets("Departures").Range(Cells(1, 1), Cells(r, c)).RemoveDuplicates Columns:=1, Header:=xlYes
End If

So i had actually wrapped it in an if statement earlier today when i was trying to figure out the reason for the crash, but it didnt seem to fix the issue so i removed it.

 

[MARK858]

Try specifying the sheet on Cells, as you would have got an error if Sheets("Departures") wasn't the activesheet.

Range(Sheets("Departures").Cells(1, 1), Sheets("Departures").Cells(r, c)).RemoveDuplicates Columns:=1, Header:=xlYes

 

[termeric]

@MARK858 That was it, thanks for the help.

 

[MARK858]

You're welcome