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

[davidm]

Pink, this will solve it:

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

[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


--
HTH

Bob Phillips

"Pink Panther" <[email protected]> wrote in message
news:[email protected]...
> 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]

"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> 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....


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>
> "davidm" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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.
>
>