Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave

If there are no embedded blanks between A1 and the last filled cell:

Range("A1").End(xlDown).Address

If there are, but there's no data below the specified range,

Cells(Rows.Count, 1).End(xlUp).Address


On Mon, 05 Nov 2007 11:30:12 -0800, Dave F wrote:

Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave

There is a bit on an issue with what you have asked for. By only specifying
A1 as the argument then the function needs to be made volatile to account for
when new values are added to column A. You can get around that by adding in
the entire column as the argument to the UDF. Give this a try.

Public Function EndAddress(ByVal rng As Range) As String
Dim rngLast As Range

With rng
Set rngLast = .Item(.Count).End(xlUp)
If rngLast.Row < .Item(1).Row Then Set rngLast = .Item(1)
End With
EndAddress = rngLast.Address

End Function

Use the function like this...
=EndAddress(A:A)
or
=endAddress(A5:A50)
--
HTH...

Jim Thomlinson


"Dave F" wrote:

Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave

Public Function EndAddress(rng As Range)
Dim r As Range, r1 As Range
Set r = rng.Areas(rng.Areas.Count)
Set r = r(r.Count)
Set r1 = r.Parent.Cells(Rows.Count, r.Column).End(xlUp)
EndAddress = r1.Address(0, 0)
End Function

would be a start.

--
regards,
Tom Ogilvy


"Dave F" wrote:

Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave

How about:

Function EndAddress(r As Range) As String
EndAddress = ""
For Each rr In r
If IsEmpty(rr) Then
Else
EndAddress = rr.Address
End If
Next
End Function

--
Gary''s Student - gsnu200753


"Dave F" wrote:

Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave

Thanks for the suggestion. Using this code I get a #VALUE! error:

Function EndAddress()
Range("A1").End(xlDown).Address
End Function

The range in this case is A1:A5 and there are no blanks in the row.

This code is entered in a module.

Thoughts?

On Nov 5, 2:56 pm, Myrna Larson
wrote:
If there are no embedded blanks between A1 and the last filled cell:

Range("A1").End(xlDown).Address

If there are, but there's no data below the specified range,

Cells(Rows.Count, 1).End(xlUp).Address



On Mon, 05 Nov 2007 11:30:12 -0800, Dave F wrote:
Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave- Hide quoted text -

- Show quoted text -

There is a limitation/advantage to .End(xlUp)
It ignores hidden cells.
This may or may not be what you want.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Tom Ogilvy" wrote in message
...
Public Function EndAddress(rng As Range)
Dim r As Range, r1 As Range
Set r = rng.Areas(rng.Areas.Count)
Set r = r(r.Count)
Set r1 = r.Parent.Cells(Rows.Count, r.Column).End(xlUp)
EndAddress = r1.Address(0, 0)
End Function

would be a start.

--
regards,
Tom Ogilvy


"Dave F" wrote:

Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave

Thanks Charles.

There are no hidden cells in the worksheets I'm working with, so that
shouldn't pose a problem.

Dave

On Nov 5, 5:26 pm, "Charles Williams"
wrote:
There is a limitation/advantage to .End(xlUp)
It ignores hidden cells.
This may or may not be what you want.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now availablewww.DecisionModels.com

"Tom Ogilvy" wrote in message

...



Public Function EndAddress(rng As Range)
Dim r As Range, r1 As Range
Set r = rng.Areas(rng.Areas.Count)
Set r = r(r.Count)
Set r1 = r.Parent.Cells(Rows.Count, r.Column).End(xlUp)
EndAddress = r1.Address(0, 0)
End Function

would be a start.

--
regards,
Tom Ogilvy

"Dave F" wrote:

Does anyone have code for a UDF that will return the ending cell of a
range.

I'm envisioning something like =EndAddress(A1) would return, say $A$10
if the range A1:A10 were populated.

Anyone ever create a UDF along these lines?

Thanks,

Dave- Hide quoted text -

- Show quoted text -