DYNAMIC RANGES

  • Thread starter Thread starter paul.sternhagen
  • Start date Start date
P

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 acolumn with a non-zero entry.
Any help is appreciated.
 
The Offset-CountA method described in the link already mentioned will work
if the column of data does not have blanks in it. If your range has blank
cells in it, the countA method will not work.

The CountA method does not require programing. The CountA method is
dynamic. I e., it will change as users change the spreadhseet.

You sound like you want a way to assign the name via programming. Do you
want this to happen during the course of a program you are running, or do
you want to have a button or menu item assign the range when you push the
button? One way to get the last cell in a column is to go to the cell in
last row of the spreadsheet, and then press the End, then the Up arrow key.
In a program it would work like this:

Dim rngStart As Range, rngLast As Range
Set rngStart = Selection
Cells(65536, rngStart.Column).End(xlUp).Select
Set rngLast = Selection
ActiveWorkbook.Names.Add Name:="PaulRange", RefersTo:= _
"=" & ActiveSheet.Name & "!" & Range(rngStart, rngLast).Address

The Name is asigned, however it is not dynamic. It does not change until
the code runs again.

John
 
Back
Top