Automatic range finder??

Posted by Augusto Clavijo on February 11, 2000 11:16 AM

Hello..
I am currently involved in trying to find a macro that will do the following things
Suppose we have an "n" of sheets.
Each one with a "x" number of rows.
I am trying to build a macro where Excel will:
Automatically find the range from C2:C("x") and assign some name to it, say "TheRange" so it can later be used in formulas and arrayformulas.
Automatically do the same process for each of the other "n" worksheets taking into account that "x" will be different in each one.

I truly hope you find and idea for me, I will truly appreciatte it.
Thank you very much.

Augusto Clavijo

Posted by Ivan Moala on February 11, 2000 2:37 PM

Hi Augusto
Try something like this

Sub Tester()
Dim Rg As Range
Dim Sh
Dim Indx As Integer

'Index of Named Range
Indx = 1

For Each Sh In ActiveWorkbook.Sheets
Sh.Select
Set Rg = Sh.Range(Range("C2"), Range("C2").End(xlDown))
ActiveWorkbook.Names.Add Name:="TheRange" & Indx, RefersToR1C1:=Rg
Indx = Indx + 1
Next
End Sub

HTH

Ivan