find last used cell (row) in a named range

[Harold Good]

Hi, I am looking for a way to find the last used cell in a named range within a column. The following will work for an entire column:

Range("A65536").End(xlup).Select

but when I change it to this,using a named Range, it will not work.

Range("amount_local").End(xlUp).Select

"Amount_Local" is part of one column.

Can anyone tell me how to do this within a named range?

Thanks,
Harold

 

[Chip Pearson]

Try

Dim LastCell As Range
With Range("Amount_Local")
Set LastCell = .Cells(.Cells.Count).End(xlUp)
End With
Debug.Print LastCell.Address


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary Keramidas]

see if this does what you need:

Sub test()
Dim lastaddr As String
lastaddr = Split(Range("amount_local").Address, ":")(1)
MsgBox lastaddr
End Sub


--


Gary K


Hi, I am looking for a way to find the last used cell in a named range within a column. The following will work for an entire column:

Range("A65536").End(xlup).Select

but when I change it to this,using a named Range, it will not work.

Range("amount_local").End(xlUp).Select

"Amount_Local" is part of one column.

Can anyone tell me how to do this within a named range?

Thanks,
Harold

 

[Harold Good]

Thanks, this gave the address of the last cell in the named range (H71), but did not find the last used cell, which is H45. There are some blank cells above it, within the named range, but I want it to find the bottom most cell with a number in it, which is H45.

Thanks for any other thoughts you may have.
Harold
see if this does what you need:

Sub test()
Dim lastaddr As String
lastaddr = Split(Range("amount_local").Address, ":")(1)
MsgBox lastaddr
End Sub


--


Gary K


Hi, I am looking for a way to find the last used cell in a named range within a column. The following will work for an entire column:

Range("A65536").End(xlup).Select

but when I change it to this,using a named Range, it will not work.

Range("amount_local").End(xlUp).Select

"Amount_Local" is part of one column.

Can anyone tell me how to do this within a named range?

Thanks,
Harold

 

[Harold Good]

Thanks Chip, this didn't seem to do anything. I presume I insert this
between a Private Sub and End Sub lines so it looks like this below. As I
stepped thru it, I didn't see anything in the Locals window, Value column.
Thanks for any other thoughts you may have. Harold Good

Private Sub LastCell()
Dim LastCell As Range
With Range("Amount_Local")
Set LastCell = .Cells(.Cells.Count).End(xlUp)
End With
Debug.Print LastCell.Address
End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

[Dave Peterson]

Debug.Print
will display in the immediate window.

Inside the VBE, you can hit ctrl-g to see that.

or use:
msgbox LastCell.Address

to get a message box.

 

[Gary Keramidas]

what is the actual address range of amount_local?

--


Gary K


Thanks, this gave the address of the last cell in the named range (H71), but did not find the last used cell, which is H45. There are some blank cells above it, within the named range, but I want it to find the bottom most cell with a number in it, which is H45.

Thanks for any other thoughts you may have.
Harold
see if this does what you need:

Sub test()
Dim lastaddr As String
lastaddr = Split(Range("amount_local").Address, ":")(1)
MsgBox lastaddr
End Sub


--


Gary K


Hi, I am looking for a way to find the last used cell in a named range within a column. The following will work for an entire column:

Range("A65536").End(xlup).Select

but when I change it to this,using a named Range, it will not work.

Range("amount_local").End(xlUp).Select

"Amount_Local" is part of one column.

Can anyone tell me how to do this within a named range?

Thanks,
Harold

 

[Harold Good]

Presently it is H29:H71, this is where I have been doing my testing. But it is a dynamic named range, so that as users have more expenses to enter, this range will grow in length (but always within Col H).

Harold
what is the actual address range of amount_local?

--


Gary K


Thanks, this gave the address of the last cell in the named range (H71), but did not find the last used cell, which is H45. There are some blank cells above it, within the named range, but I want it to find the bottom most cell with a number in it, which is H45.

Thanks for any other thoughts you may have.
Harold
see if this does what you need:

Sub test()
Dim lastaddr As String
lastaddr = Split(Range("amount_local").Address, ":")(1)
MsgBox lastaddr
End Sub


--


Gary K


Hi, I am looking for a way to find the last used cell in a named range within a column. The following will work for an entire column:

Range("A65536").End(xlup).Select

but when I change it to this,using a named Range, it will not work.

Range("amount_local").End(xlUp).Select

"Amount_Local" is part of one column.

Can anyone tell me how to do this within a named range?

Thanks,
Harold

 

[Harold Good]

Ok, I now see in the Immediate window that it properly finds H45. Thanks!!

How do I get it from this stage, to where I can select it and do an Offset
so I can then sort the resulting range?
Sorry, I'm a real VBA novice!

Thanks again,
Harold