Last cell in column (with hidden rows)

[Pink Panther]

I have a problem. and don't know how to solve it.....

First the situation....
I have a table on a worksheet. Every row in that table contains data but
some rows are hidden. Even the last row of that table could be hidden.
Now I want to add a new row (at the end) of the table, but when I do this by
using the code (see below), I goes wrong when the last row in the table is
hidden.
The problem is that the hidden row will be overwritten.....

ActiveCell.Copy Destination:=Range("Base_KT").End(xlDown).Offset(1, 0)

Thanks in advance,

Paul Bleijlevens

PS: Range("Base_KZT) is the named cell in the upper left corner of the
table.....

 

[STEVE BELL]

See if this works better:

Set "A" = to column of Base_KT

Dim LASTROW As Long

LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Offset(1, 0).Row

ActiveCell.Copy Destination:=Cells(LASTROW,Range("Base_KT").Column)

 

[Norman Jones]

Hi Pink Panther,

Try the following which should work for visible or hidden rows:

'=============================>>
Sub TestIt()
Dim Lrow As Long, FirstCol As Long

Lrow = LastRow(, Range("Base_KZT").CurrentRegion)

FirstCol = Range("Base_KZT").Column

ActiveCell.Copy Destination:= _
Cells(Lrow + 1, FirstCol)

End Sub
'<<=============================

'=============================>>
Function LastRow(Optional sh As Worksheet, Optional rng As Range)
If sh Is Nothing Then Set sh = ActiveSheet
If rng Is Nothing Then Set rng = sh.Cells

On Error Resume Next
LastRow = rng.Find(What:="*", _
After:=rng(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<=============================