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

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)



On Wed, 18 Feb 2009 16:01:25 -0600, "Harold Good"
wrote:

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

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


"Harold Good" wrote in message ...
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

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
"Gary Keramidas" wrote in message ...
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


"Harold Good" wrote in message ...
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

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

"Chip Pearson" wrote in message
...
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)



On Wed, 18 Feb 2009 16:01:25 -0600, "Harold Good"
wrote:

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

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.


Harold Good wrote:

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

"Chip Pearson" wrote in message
...
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)



On Wed, 18 Feb 2009 16:01:25 -0600, "Harold Good"
wrote:

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

--

Dave Peterson

what is the actual address range of amount_local?

--


Gary K


"Harold Good" wrote in message ...
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
"Gary Keramidas" wrote in message ...
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


"Harold Good" wrote in message ...
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

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
"Gary Keramidas" wrote in message ...
what is the actual address range of amount_local?

--


Gary K


"Harold Good" wrote in message ...
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
"Gary Keramidas" wrote in message ...
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


"Harold Good" wrote in message ...
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

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


"Dave Peterson" wrote in message
...
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.


Harold Good wrote:

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

"Chip Pearson" wrote in message
...
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)



On Wed, 18 Feb 2009 16:01:25 -0600, "Harold Good"
wrote:

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

--

Dave Peterson