Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EXCEL CODE (English/Excel/2003)

    My Company has a program that pulls data from an Excel roster. The length of this roster changes on a daily basis. They are currently going into the VBA to adjust the range of the roster from which the data is pulled. Is there a code that will automatically adjust to length of the roster?

    I have attached to current code with the portion in question highlighted in yellow.
    Charlie

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    The following will select all of column C from C2 to the bottom assuming there is no data below row 602 (using your example macro as the limit in this case).

    Range(Cells(2, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3)).Select

  3. #3
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    Thank you for your quick response. We are not very smart with VBA. If our data sometimes goes beyond 602 could I change that number to a number that the Company would never reach, say 1000, and the code work? Do I replace the current code with what you have provided or do I add it in somewhere?
    Charlie

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: EXCEL CODE (English/Excel/2003)

    The line

    Range("C2:C602").Select

    isn't used because it is followed immediately by

    Range("D2").Select

    The same goes for the line

    Range("D2602").Select

    The ones that are actually used, are

    Selection.AutoFill Destination:=Range("C2:C602"), Type:=xlFillValues
    Selection.AutoFill Destination:=Range("D2602"), Type:=xlFillValues

    You could change this as follows:

    Dim m As Long
    m = Range("C" & Rows.Count).End(xlUp).Row
    Selection.AutoFill Destination:=Range("C2:C" & m), Type:=xlFillValues
    ...
    Selection.AutoFill Destination:=Range("D2" & m), Type:=xlFillValues

  5. #5
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    Hans, thanks for the quick reply. I will suggest your changes and see if they work. You guys are awesome.
    Charlie

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    I've attached a shorter version of your macro with Hans' suggestion built into it. I removed sections that had the macro selecting and then performing actions on those selections where possible. (there may be other ways to shorten it as well).

    For example this section:

    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Selection.Insert Shift:=xlToRight


    can be replaced with

    Columns("C").Insert
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    Thank you. Working with the end user now to see if it what they want.
    Charlie

  8. #8
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    Hans, this suggestion worked great and fixed our problem. Thanks for your help.
    Charlie

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: EXCEL CODE (English/Excel/2003)

    You're welcome!

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: EXCEL CODE (English/Excel/2003)

    This <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> may be throwing a spanner into the works, but often a good way of dealing with a range of varying/unknown size is to use Named Ranges (see the section on Dynamic Ranges). Of course, this would involve (some small) redesign of the workbook, but may be a good investment for the future, as other types of report types are needed.

    Alan

Posting Permissions

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