Excel 2007 range creation help

[mdriver]

Hello Everyone,
I would like some advice of what direction to go with the below please:

The inserted spreadsheet below consists of a named range, "CompTable1", and ultimately
copy, offset and PasteSpecial the information to what will be a named range "PrintTable1".
Easy enough and it works beautifully with lots of research, trial and error and help from my fellow board members.

As you can see, the first column of data is offset 6 columns left, the second 5 columns left and the third 5 columns left.

After this action is completed, I have an input box allowing the user to select the new range (for example, J9:N12), then selecting a pre-defined name, "PrintTable1", from a ComboBox. This named range is stored in a Public variable for future formatting/processing from other modules.

Again, works great but the input box selection seems a little redundant to me. The new range is always going to be fixed to 5 columns wide, be offset 6 columns left to begin, with the 2nd and 5th column blank for later processing, and be in line (as far as rows (for example, row 9) are concerned) with "CompTable1".

What I am wishing to work out is how to forgo the input selection box and proceed by selecting a name, "PrintTable1", and have my code reference the newly created range limits (For example, J9:N12).

Some other useful pieces of information:
This procedure currently works up to 10 CompTableX and 10 PrintTableX. Can expand later.
The blank cells are processed later with up, down or zero percent arrows based on certain criteria from the CompTable.
I use a With rangename.Cells(R,C).resize(rangename.rows.count) scenario to locate the last row in the "CompTable1" range. This how I copy, offset and PasteSpecial the values in each column.

Should I somewhow store this information in an dynamic array, preserve that array, redim then pass it to my selected combobox public variable name?
If so not sure what steps to begin. I have read how to pass a known range into an array but not one where the range is ideally not known.

Please help me get started.

Thanks,
Mark Driver

Excel 2007
	J	K	L	M	N	O	P	Q	R	S
6	Print Table 1
7	DERBY DRIVE SE RATES						Comp. Table 1
8	STATION	LEFT		RIGHT				LEFT	RIGHT
9	10+70.00		0.020	0.020			10+70.000000	-2.0000%	-2.0000%	10+70.000000
10	10+75.50		0.017	0.000			10+75.500000	-1.7000%	0.0000%	10+75.500000
11	10+81.00		0.020	0.020			10+81.000000	-2.0000%	-2.0000%	10+81.000000
12	11+00.00		0.020	0.020			11+00.000000	-2.0000%	-2.0000%	11+00.000000
Road A

 

[mdriver]

I solved my own problem on the back of a MooYah receipt in blue crayon my youngest "let me borrow" tonight!
This is how approached it.

Set UserRange = r.Cells(1, 1).Offset(0, -6).Resize(r.Rows.Count, 5)

PrintTableList.Show

UserRange.Name = PrintStrList

Set UserRange = Range(PrintStrList)

I was originally prompting the user to select the range to name after populating a predefined combobox.
This seems more efficient to me.
Thanks,
Mark Driver