UDF to find end of range

[Dave F]

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

 

[Myrna Larson]

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

 

[Guest]

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)

 

[Guest]

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.

 

[Guest]

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

 

[Dave F]

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?

 

[Charles Williams]

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

 

[Dave F]

Thanks Charles.

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

Dave