Last cell in column (with hidden rows)
 

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.....

Last cell in column (with hidden rows)
 

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 )
--
steveB

Remove "AYN" from email to respond
"Pink Panther" wrote in message
...
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.....

Last cell in column (with hidden rows)
 

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
'<<=============================

---
Regards,
Norman



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