Appending Ranges

  • 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:

    Dim range1 as Range
    Set Range1 = Rows("9:41")

    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.

  • Re: Appending Ranges

    As a side, how do you know the range2 starts at 230? Is there a method to finding the start/end for each range? For instance, if you inserted a row (so Rng2 starts on 231), saved the workbook and then reopened, how would the user (and therefore your code) know range2 starts at 231?

    If you attach a spreadsheet it will help.

  • Re: Appending Ranges

    I have attached an abbreviated version of the document in question.

    I have a dropdown data validation menu that when it gets changed the correct range of cells becomes visible. IE if range1 is selected then cells 9 thru 230 are visible and any rows higher than 230 are hidden, same for range2 where rows 9-230 are hidden as well as anything above 300.

    Currently this set to a static number of data input cells, but then I was thinking what if the user wants to add another data row. It is not simply a matter of inserting another row it is a matter of inserting anywhere from 2-7 rows of data with formulas and formatting. This is what is prompting the dynamic ranges as users add rows.

    I know what the ranges are and that is what matters, the users are not going to be touching the code for this. There is no way of knowing what the start value is for where range1 or range 2 begins, A default number of data entry rows will be in the document to begin with. I will set the starting range and then adjust it based on the number of times the user presses the insert data row button.

    The rows in between the ranges are completely blank with no formatting. It could be for range 1 start at cell B9 and count down until B??? is blank and that would be range 1, range 2 would then be end of range 1 + 1 row until the next blank.

    Not sure if this helps.

    In the attached file I have an abbreviated data set and workbook. There are references in formulas to a sheet that does not exist in this workbook, but that should not affect anything that I am looking for,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!