I have the following code that is supposed to remove account numbers, from either the "Master" Sheet or the Sheet "41Days" based on the account number and the date. If I remove the second criteria in the countifs, this code runs fine but deletes the duplicate account numbers from only one sheet or the other depending on if I comment out one of the two "If isarray" statements. I need to be able to delete the account numbers from the correct sheet based on if it's a duplicate and it meets the date requirements.
Code
Option Explicit
Sub DupsByDate()
Dim AccountNumbersRange As Range, searchRange As Range, AccountNumArr
Dim x As Long, SearchRange2 As Range, FortyTwoDaysPrior As Date, CurrentDate As Variant
Dim FortyOneDaysSheet As String: FortyOneDaysSheet = "41Days"
Dim FortyOneDaysCol As String: FortyOneDaysCol = "A"
Dim MasterSheetName As String: MasterSheetName = "Master"
Dim MasterSheetCol As String: MasterSheetCol = "A"
Dim MasterSheetCol2 As String: MasterSheetCol2 = "B"
CurrentDate = Format(Date, "mm/dd/yyyy")
FortyTwoDaysPrior = DateAdd("d", -42, CurrentDate)
With Sheets(FortyOneDaysSheet)
Set AccountNumbersRange = .Range(.Range(FortyOneDaysCol & "2"), _
.Range(FortyOneDaysCol & Rows.Count).End(xlUp))
AccountNumArr = AccountNumbersRange
End With
With Sheets(MasterSheetName)
Set searchRange = .Range(.Range(MasterSheetCol & "2"), _
.Range(MasterSheetCol & Rows.Count).End(xlUp))
End With
With Sheets(MasterSheetName)
Set SearchRange2 = .Range(.Range(MasterSheetCol2 & "2"), _
.Range(MasterSheetCol2 & Rows.Count).End(xlUp))
End With
If IsArray(AccountNumArr) Then
For x = UBound(AccountNumArr) To 1 Step -1
If Application.WorksheetFunction.CountIfs(searchRange, AccountNumArr(x, 1), SearchRange2, ">" & FortyTwoDaysPrior) Then
AccountNumbersRange.Cells(x).EntireRow.Delete
End If
Next
Else
If Application.WorksheetFunction.CountIfs(searchRange, AccountNumArr, SearchRange2, ">" & FortyTwoDaysPrior) Then
AccountNumbersRange.EntireRow.Delete
End If
End If
If IsArray(AccountNumArr) Then
For x = UBound(AccountNumArr) To 1 Step -1
If Application.WorksheetFunction.CountIfs(searchRange, AccountNumArr(x, 1), SearchRange2, "<=" & FortyTwoDaysPrior) Then
searchRange.Cells(x).EntireRow.Delete
End If
Next
Else
If Application.WorksheetFunction.CountIfs(searchRange, AccountNumArr, SearchRange2, "<=" & FortyTwoDaysPrior) Then
searchRange.EntireRow.Delete
End If
End If
End Sub