You do an excellent job!

Thanks for your time and knowledge.

Gary''s Student wrote:

As you have already noticed, variables are usually NOT preserved. However,
if you Dim the variable above the Sub statement, they will be preserved.

For example:

Dim OldRowCount As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(OldRowCount) Then
OldRowCount = ActiveSheet.UsedRange.Rows.Count
Else
MsgBox ("there are " & ActiveSheet.UsedRange.Rows.Count & " rows")
MsgBox ("there were " & OldRowCount & " rows")
OldRowCount = ActiveSheet.UsedRange.Rows.Count
End If
End Sub

1. notice the Sub follows the Dim (opposite of the usual case)
2. we don't need a function, just a variable
3. because OldRowCount is a memory variable, it won't have a value the very
first time the sub is called, so I test for this.
4. you could also have Dim'ed the variable by itself in a standard module