Sorry about the delay everyone. I've just got back from holiday.
I ended up going with the MATCH and COUNTIF option on a temporary sheet. Thanks for the initial advice Gavin, and to Skip for explaining it perfectly.
I've posted my code as I think it could be useful to others. I'd imagine knowing what rows are missing (rather than how many) is a great starting point to debugging your code.
Cheers,
Knifey
'this sub finds what row numbers are missing.
'wBook2 is the workbookname where the raw data is found.
'iSLASheet is the sheetname where the raw data is found.
'wb2endRow is the last row number of the raw data.
Sub findMissingRows(ByVal wBook2 As Workbook, iSLASheet As String, wb2endRow As Long)
Dim c As Range
Dim cRange As Range
Dim pRange As Range
Dim wBook1 As Workbook
Application.ScreenUpdating = False
Set wBook1 = ThisWorkbook
With wBook1.Worksheets
If Not SheetExists("tempMissingRows") = True Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "tempMissingRows"
Else
wBook1.Worksheets("tempMissingRows").Activate
End If
End With
Set pRange = wBook1.Worksheets("tempMissingRows").Columns("A").Rows("2:" & wb2endRow)
wBook1.Worksheets("tempMissingRows").Range("A1").Value = "Input Row Number"
wBook1.Worksheets("tempMissingRows").Range("B1").Value = "Data"
wBook1.Worksheets("tempMissingRows").Range("C1").Value = "Adjustments"
wBook1.Worksheets("tempMissingRows").Range("D1").Value = "Discrepancies"
wBook1.Worksheets("tempMissingRows").Range("E1").Value = "Found?"
With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("1").Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("1")
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("2:" & wb2endRow)
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With
With wBook1.Worksheets("tempMissingRows").Columns("A:E").Rows("1")
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = xlAutomatic
End With
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 7.43
Columns("C:C").EntireColumn.AutoFit
Columns("D

").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
wBook1.Worksheets("tempMissingRows").Range("A2").Value = 2
wBook1.Worksheets("tempMissingRows").Range("A3").Value = 3
wBook1.Worksheets("tempMissingRows").Range("A2:A3").Select
Selection.AutoFill Destination:=pRange, Type:=xlFillDefault
Range("A1").Select
With Worksheets("tempMissingRows")
For Each c In pRange
c.Offset(0, 1).Value = Application.Match(c.Value, (Worksheets("Data").Columns("A")), 0)
c.Offset(0, 2).Value = Application.Match(c.Value, (Worksheets("Adjustments").Columns("A")), 0)
c.Offset(0, 3).Value = Application.Match(c.Value, (Worksheets("Discrepancies").Columns("A")), 0)
c.Offset(0, 4).Value = WorksheetFunction.CountIf(Worksheets("tempMissingRows").Columns("B:E").Rows(c.Row), "<>#N/A")
Next c
End With
If WorksheetFunction.CountIf(Worksheets("tempMissingRows").Columns("E"), "1") = 0 Then
Application.DisplayAlerts = False
Worksheets("tempMissingRows").Delete
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub