I am just starting to write the code for this and I wanted to get some input on the best way to do this. I have multiple ranges of data that are laid out underneath each other. IE range1 is rows 9-41, range2 is 42 to 100, etc.

Dim rng1 as Range

Q1: I am new to using a variable as a range so how would I define the range to be Rows 9 thru 41

Here is the code I am using for this question:

Q2: How would I select the second to last row plus 2 rows above it (total of 3 rows)

Q3: How would I select the last row of this range and insert copied cells above it.

Q4: By inserting these rows into it how do I redefine the range to include the inserted rows. Range 1 would then be 9 thru 233 after the insert. This would also adjust all subsequent ranges by the same value.

Q5: This is related but a slightly different circumstance. I have a range of cells that takes the data from every third row (Totals row) of the above listed range and places them on a separate sheet which is used for making charts with dynamic ranges. Anyway the cell references are Sheet1!C15 and the next cell down is Sheet1!C18, as i drag the formulas to subsequent cells they only increase by a value of 1, is there anyway to do this where the next one is always a set number of cells from the previous entry.

Thanks in advance for the help.