Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing named ranges from a reference workbook (Excel 97)

    Hopefully someone here can offer me some help. Here's what I need to do...

    I want to maintain a single Excel file with many different chunks of data in named ranges that will be used to compile a dynamically created Excel file on the fly depending on what data is required in that new file. The named ranges in the 'reference' file will have a variety of row lengths, but generally always the same number of columns. The new "dynamic" file will either start by prompting the user for a product number, for instance, and then look up that product number in the reference file, determine all the related data that is needed (i.e. VLOOKUP) and then import the particular ranges of data into the new file, appending the ranges one after the other. The alternative is to open a dialog box to prompt the user for all the options that this product number requires (i.e. radio buttons or check boxes), and when they select "OK", the requires ranges of data are imported. Since the reference ranges will be of various row lengths I need the import process to automatically append the ranges one after the other, regardless of what row the previously imported range ends on. BTW, some of the imported ranges will have empty cells.

    MUCH THANKS to any and all who can provide me some direction here. I have difficulty learning new procedures in VBA.

    Drew

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Importing named ranges from a reference workbook (Excel 97)

    linux_man

    I don't know if MS-Excel runs on LINUX, but what the heck.

    I think you need to look at the following line of code

    <font color=blue> Dim lFirstAviailabeRow As Long
    lFirstAviailabeRow = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Row </font color=blue>


    Now what this does is that it goes from cell A1 all the way down until it finds an blanck cell. And stops. Then it advances one row, to the empty one, and return that row number.

    So you can then paste at that row number and that solves the problem.

    Now it is obvious that this depends on blanck cells, and it is obvious that if Column A has the most data, then you will be fine, but you need to know which column to use to keep this going without overwriting data.

    Also what if column A is ALL blanck, well that sends you all the way to the last Row 65536. So then you will do the opposite, you go up:

    <font color=blue> lFirstAviailabeRow = ActiveSheet.Range("A1").End(xlUP).Row </font color=blue>

    OK I hope I answered your question.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing named ranges from a reference workbook (Excel 97)

    The code below will find the row number of the first row after the last row of data on the sheet:

    <pre>Dim lNextRow As Long
    lNextRow = Worksheets("Sheet1").Range("A1").Offset(Worksheets ("Sheet1").UsedRange.Rows.Count, 0).End(xlUp).Row + 1
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing named ranges from a reference workbook (Excel 97)

    Wassim: I don't believe that your code will properly handle the situation that was mentioned in the original question that not all cells in the used rows contain data.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing named ranges from a reference workbook (Excel 97)

    That is correct. There will be cases when the previously imported data may have blank cells in the left most column.
    Basically, I want to create a worksheet template and a form that will provide checkboxes for options. The user will select the various options for data that is required in a new worksheet. When the user selects OK, the code will then import the appropriate ranges of data from an external workbook and place them neatly in order within the new template. Again, some of the imported data may have empty cells here and there.

    I have attached a zip file of worksheets as examples. If anyone can provide me some insight as to how to get this started, I would be very grateful.

    The three files in the ZIP are a reference file, containing all the standard data ranges that may be available, the template file that will be created dynamically based on the user selecting options in a form, and the end result file which shows an example of how it should come together in a finished product.

    Drew
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing named ranges from a reference workbook (Excel 97)

    Not sure quite how to do it all, but rather than try to find the last Row used I would make a lookup table listing the rows used in a block and calculate the row number requierd from that

    HTH

    Peter

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing named ranges from a reference workbook (Excel 97)

    Actually, I have tried something that would work well with your idea. One can use the simple ROWS function to determine the number of rows in a named range. Knowing this, for each imported range, one could simply add up the number of rows in each and use that value for further range imports. Thanks for the tip.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •