Find last nonblank cell in column
 

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

Find last nonblank cell in column
 

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


--
HTH

Bob Phillips

"Pink Panther" wrote in message
l...
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

Find last nonblank cell in column
 

Pink, this will solve it:

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


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=388637

Find last nonblank cell in column
 

"davidm" wrote in
message ...

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.

Find last nonblank cell in column
 

Thanks, this would do the job....


"Bob Phillips" wrote in message
...

"davidm" wrote in
message ...

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.