Selecting a changing area

[BillLambeer]

321BB_dec02.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		2001/08	2001/09	2001/10	2001/11	2001/12	2002/01	2002/02	2002/03	2002/04	2002/05	2002/06	2002/07	2002/08	2002/09	2002/10	2002/11	2002/12	Average
2	Month0
3	Month1					1			1		1			4		2
4	Month2				1				8	1		3	2	2
5	Month3			1	1		5	1	2	3	1
6	Month4				1	4	6		4	2	5	4		3
7	Month5		1		6	4	2	4	5	3	5	2	2
8	Month6	1			7	2	3	5	3	3	3	1
9	Month7		5	2	2	2	4	2	6	1	1
10	Month8		6	3	4	2	5	1	3	4
11	Month9		3	3	4	11	3	2	4
12	Month10		2	2	15	7	1	3
13	Month11		2	16	9	8	2
14	Month12		16	9	14	1
15	Month13		7	11	1
16	Month14		11	1
Reliability

I have the above spreadsheet and I need to select B2:R16 in this case. B2 is always the fist cell and 16 is always the last row. However the column changes as I add months and I'm not sure how to find the end. What I do is select the whole range and paste a VLOOKUP formula in. Then I replace all of the #N/A with "" so the other formulas will work. As you can see there is not always data in the current month so I can't use

Range("B2", Range("B16").End(xlRight)).Select

Please ask me if you need any clarification. Thanks this board is great.

 

[DonkeyOte]

if you're doing this in VB then first do

Cells(1,1).select
COL = Cells.End(xlRight).Column
RW = Cells.End(xlDown).Row

then your range will be

Range(Cells(2,2),Cells(RW,COL)) = "=vlookup..."

 

[BillLambeer]

On 2003-01-24 10:59, lasw10 wrote:
if you're doing this in VB then first do

Cells(1,1).select
COL = Cells.End(xlRight).Column
RW = Cells.End(xlDown).Row

then your range will be

Range(Cells(2,2),Cells(RW,COL)) = "=vlookup..."

I could not get the above code to work. It gave me an error on the line where COL is set to the last column. I have been experimenting a bit with the code below.

With Worksheets("Reliability").Range("A1:EZ1")
Set c = .Find("Average", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


ActiveCell.Offset(rowOffset:=15, columnOffset:=-1).Activate
ED = ActiveCell.Column
Range(Cells(2, 2), Cells(16, ED)).Select

Does this code seem like it will work?

 

[DonkeyOte]

the correct syntax should be

COL = Cells.End(xltoRight).Column

Then it will work.