The last blank cell containing the value of "B9" does not rng.Select. Much
appreciate if someone could advise me as to why.

If IsEmpty(Range("b9")) Then
MsgBox "No record found in B9.", vbInformation
Else
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

If lngLastRow <= 10 Then
Range("B10").Value = Range("B9").Value
Else
Cells(lngLastRow, "B").Value = Range("B9").Value
If rng Is Nothing Then
Else
rng.Select
End If
End If
End If

End Sub