Results 1 to 12 of 12
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting Range? (Excel 2003)

    Don't you mean "... for a column that is variable)? You can use

    Range(Cells(1, 3), Cells(1, ColCnt))

    or

    Range("C1").Resize(1, ColCnt - 2)

    (and the method to select a range is .Select, not .Selection)

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting Range? (Excel 2003)

    See Column Numbers To Letters for a function that convets the number to the corresponding column letters.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Range? (Excel 2003)

    Thanks Hans,

    Exactly what I need.

    (Will edit my question so it doesn't confuse someone else.)
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting Range? (Excel 2003)

    Can anyone tell me the format for setting a Range for a Columns that are variable.

    I have determined the numeric position of the last column and stored it in a variable called ColCnt. If it is 7 then I want Cell 'G1' if it is 27 then 'AA1'

    What I need is if ColCnt = 7 then

    Range("C1:G1").select

    Thank you
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Range? (Excel 2003)

    Thank you Tony.

    This has now become part of my Excel Library.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Range? (Excel 2003)

    Hi Hans:

    I find that this cells syntax within the Range method fails when I automate Excel from Access via VBA using Office 2003 on Windows XP.

    For example:

    Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True

    and

    Set rng = osheet.Range(Cells(1, 1), Cells(1, intCols))

    both fail. Any idea why or how to fix it?

    Thanks!

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Setting Range? (Excel 2003)

    Just guessing but have you tried..

    Worksheets("Sheet1").Range(Range(Cells(1, 1), Cells(5, 3))).Font.Italic = True

    ..I seem to remember someting about having to use Range twice occsionally

    zeddy

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Setting Range? (Excel 2003)

    Kathryn,
    You need to prefix the Cells calls with oSheet too:
    <code>Set rng = osheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, intCols))</code>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Range? (Excel 2003)

    No, but I figured it out:

    You have to activate the sheet object first. Then, it works fine.

    (Why, you ask????)

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Setting Range? (Excel 2003)

    By default, <code>Cells</code>, like <code>Range</code>, refers to the active sheet, therefore they all need to be prefixed with the sheet object (or you can activate the sheet, but it's better practice to use the worksheet object).
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Setting Range? (Excel 2003)

    As rory mentioned, i would not activate the sheet. i would use the sheet object explicitly:

    Set rng = osheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, intCols))

    Or use a with construct:
    With osheet
    Set rng = .Range(.Cells(1, 1), .Cells(1, intCols))
    end with

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Range? (Excel 2003)

    I appreciate this hint on not activating the worksheet. It really is more efficient (and easier to understand).

    Thanks

Posting Permissions

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