dynamic ranges

[paul.sternhagen]

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.

 

[Guest]

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