Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Austin, TX,USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Ranges (VBA, Excel97)

    The WHO'S ONLINE registry indicates I am a lurker. Didn't have anything to contribute, so I just hide and watch.
    But now that I need some help, I decided to try my voice.
    What I need is a way to select a range of cells within a column. Through different iterations, this range will vary.
    This time the range may be 10 cells, next time it may be 30, then 18, etc. Everything I have tried so far ends up with
    a specified range of cells. What is the answer.

    Thank You,
    HAVE A NICE DAY

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selecting Ranges (VBA, Excel97)

    Former Lurker, welcome to the Lounge

    Could you give some idea of the criteria involved in selecting the range, as without knowing what you are trying to achieve it is difficult to suggest an answer.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selecting Ranges (VBA, Excel97)

    Welcome out of lurking. Aren't you excited to have a new title! <img src=/S/joy.gif border=0 alt=joy width=23 height=23> Is this what you had in mind? HTH --Sam
    <pre>Option Explicit
    Sub Macro1()
    Dim rngFirst As Range
    Dim n As Long
    Set rngFirst = Application.InputBox("Select top cell", , , , , , , 8)
    n = Application.InputBox("How many cells", , , , , , , 1)
    Range(rngFirst, rngFirst.Offset(n - 1, 0)).Select
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Austin, TX,USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Ranges (VBA, Excel97)

    I found it. The range selecting Easter egg in a nutshell.
    A SUB with one line of code, very concise.
    I decided to take a perusing journey with GOOGLE as my guide.
    At (http://www.mindspring.com/~tflynn/excelvba.html), under a
    link called SELECTING, the routine was posted by TOM FLYNN.

    HAVE A NICE DAY Y'ALL
    cdogdenj@swbell.net

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Austin, TX,USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Ranges (VBA, Excel97)

    Please forgive me. In an abrupt spurt of haste, I overlooked
    posting the code. Well, anyway,here it is;

    Sub SelectDown()
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    End Sub

    The routine selects a range of cells from the current cell
    to the next non-blank cell below, however far it may be.

    cdogdenj@swbell.net

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

    Re: Selecting Ranges (VBA, Excel97)

    If the upper left corner of the range you want to select is D5, and the number of rows you want to select in in variable I, and the number of columns is in J, then the following would select the range:

    <pre> Range(Range("D5"), Range("D5").Offset(I - 1, J - 1)).Select
    </pre>


    That said, one additional comment. It is almost never necessary to select cells in VBA, and it always makes the VBA code run much slower if you do. If there is not a good reason to select the cells in VBA, it is always best not to.
    Legare Coleman

Posting Permissions

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