snidera
09-08-2006, 01:21 PM
I've searched all over and can't find what i need. I hope somebody here can help.
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information
What i've tried::banghead:
Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code).
The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
ReDim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Lists!Cells(2,i+7),0,0,COUNTA(*****),1)", Visible:=True
Next i
Basically, I dont know how to get:
COUNTA(cells(2,i+7):Cells(last row,i+7))
as a range that excel understands
Any ideas? Thanks in advance
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information
What i've tried::banghead:
Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code).
The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
ReDim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Lists!Cells(2,i+7),0,0,COUNTA(*****),1)", Visible:=True
Next i
Basically, I dont know how to get:
COUNTA(cells(2,i+7):Cells(last row,i+7))
as a range that excel understands
Any ideas? Thanks in advance