dynamic ranges
 

I am looking for a means by which to select a range of cells
dynamically, by prompting excel to look for the last non-zero cell in a
column of data. My goal is to apply a name to a range of cells, but I
need to be sure that I capture all of the data in a column, so I am
hoping to write code that will allow me to specify the range of cells
dynamically, ending on the last cell in a column with a non-zero entry.
Any help is appreciated.

dynamic ranges
 

Public Function LastCell(ByVal strColumn As String, Optional wks As
Worksheet) As Range
Dim rngLast As Range

If wks Is Nothing Then Set wks = ActiveSheet

Set rngLast = wks.Cells(Rows.Count, strColumn).End(xlUp)
Do While rngLast.Value = 0 And rngLast.Row 1
Set rngLast = rngLast.Offset(-1, 0)
Loop
Set LastCell = wks.Range(wks.Range(strColumn & "2"), rngLast)
End Function

Sub test() 'Run this code...
Dim rng As Range

On Error Resume Next
Set rng = LastCell("c")
If Not rng Is Nothing Then rng.Select
End Sub

--
HTH...

Jim Thomlinson


" wrote:

I am looking for a means by which to select a range of cells
dynamically, by prompting excel to look for the last non-zero cell in a
column of data. My goal is to apply a name to a range of cells, but I
need to be sure that I capture all of the data in a column, so I am
hoping to write code that will allow me to specify the range of cells
dynamically, ending on the last cell in a column with a non-zero entry.
Any help is appreciated.