automatically finding the first empty cell
Hi Tom,
heavy thanks to you as well :o)
Have a nice day!
Markus
Function ShowEmpty(rng1 As Range)
Dim rng As Range
'Application.Volatile
Set rng = Nothing
'Set rng1 = Range("demand")
If IsEmpty(rng1(1)) Then
Set rng = rng1(1)
ElseIf IsEmpty(rng1(2)) Then
Set rng = rng1(2)
Else
Set rng = rng1(1).End(xlDown)(2)
End If
' now rng holds a reference to the first empyt cell
' rng.row gives the row
' rng.row - rng1(1).row +1 gives the nth number
ShowEmpty = "absolute row: " & rng.Row & vbNewLine _
& "nth row: " & rng.Row - rng1(1).Row + 1
End Function
--
|