Find last nonblank cell in column

[Pink Panther]

I have a small problem and don't know how to solve it...
Let's say I have range A1:A20 filled with some values and want to add a new
value in A21 but A19:A20 is hidden.
I tried range("A1.end(xlDown).offset(1,0)="hallo", but the result of this
is that I fill range("A19") with "hallo" instead of range("A21")

How can I solve this little problem without unhiding those rows???

Thanks in advance,

Paul Bleijlevens

 

[Bob Phillips]

Paul,

Try this

Range("A" & LastRow(Range("A1:A20")) + 1) = "hallo"

Function LastRow(rng As Range)
On Error Resume Next
LastRow = 1
With rng
LastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
End With
End Function

 

[davidm]

Pink, this will solve it:

Sub n()
Cells(Application.CountA(Range("a:a")) + 1, "a") = "halo"
End Sub

 

[Bob Phillips]

Pink, this will solve it:

Sub n()
Cells(Application.CountA(Range("a:a")) + 1, "a") = "halo"
End Sub

Not if the range includes some blank entries.

 

[Pink Panther]

Thanks, this would do the job....

Not if the range includes some blank entries.