Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges | Excel Discussion (Misc queries) | |||
Dynamic ranges | Charts and Charting in Excel | |||
dynamic ranges | Excel Worksheet Functions | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Dynamic Ranges Q | Excel Programming |