A couple of changes to ignore case in name comparisons, and to avoid an error if the number of adds or drops is zero:
Sub AddRem(rCur As Range, rOld As Range, rAdd As Range, rDrp As Range)
Dim nCur As Long ' current students
Dim nOld As Long ' prior students
Dim asAdd() As String ' array of new
Dim asDrp() As String ' array of drops
Dim iCur As Long ' index to current students
Dim iOld As Long ' index to prior students
Dim nAdd As Long ' number of new students
Dim nDrp As Long ' number of drops
nCur = rCur.Count
nOld = rOld.Count
ReDim asAdd(1 To nCur) ' max adds is everyone here this year
ReDim asDrp(1 To nOld) ' max drops is everyone here last year
' clear output area
rAdd.Resize(nCur).ClearContents
rDrp.Resize(nOld).ClearContents
' sort both lists
rCur.Sort Key1:=rCur(1), Order1:=xlAscending, MatchCase:=False, Header:=xlNo
rOld.Sort Key1:=rOld(1), Order1:=xlAscending, MatchCase:=False, Header:=xlNo
iCur = 1
iOld = 1
Do
Select Case StrComp(rCur(iCur).Text, rOld(iOld).Text, vbTextCompare)
Case -1 ' cur < old; current is new
'rCur(iCur).Select ' debug
nAdd = nAdd + 1
asAdd(nAdd) = rCur(iCur).Text
iCur = iCur + 1
Case 0 ' cur = old; continuing student
iCur = iCur + 1
iOld = iOld + 1
Case 1 ' old < cur; old is a drop
'rOld(iOld).Select ' debug
nDrp = nDrp + 1
asDrp(nDrp) = rOld(iOld).Text
iOld = iOld + 1
End Select
If iCur > nCur Or iOld > nOld Then Exit Do
Loop
' add any remaining current students to add list
For iCur = iCur To nCur
nAdd = nAdd + 1
asAdd(nAdd) = rCur(iCur).Text
Next iCur
' add any remaining old students to drop list
For iOld = iOld To nOld
nDrp = nDrp + 1
asDrp(nDrp) = rOld(iOld).Text
Next iOld
' output each list if not empty
If nAdd > 0 Then
ReDim Preserve asAdd(1 To nAdd)
rAdd.Resize(nAdd).Value = WorksheetFunction.Transpose(asAdd)
End If
If nDrp > 0 Then
ReDim Preserve asDrp(1 To nDrp)
rDrp.Resize(nDrp).Value = WorksheetFunction.Transpose(asDrp)
End If
End Sub
Bookmarks