Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Go to row under selected region?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Rhode Island
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In a macro, I need to go the first row under the Current region, and insert totals. Is there quick way to do this? (At least the part about going to the row under the selected current region)

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    This should get you started

    '------
    Sub test()
    Dim x

    x = Selection.Row + Selection.Rows.Count
    Rows(x).Select

    End Sub
    '------

    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Rhode Island
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Jay.

    Any chance you could walk me through what exactly this code says?

    Lori

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 11:32, LoriD wrote:
    Thanks, Jay.

    Any chance you could walk me through what exactly this code says?

    Lori
    Hi Lori,

    In the routine, I assumed that you had selected your range on the worksheet prior to running. If this is not the case, that can easily be amended.

    So, whatever you selected is understood in the code as the Selection (automatically known by Excel).

    Try this to see.

    Sub test2()
    MsgBox Selection.Address
    MsgBox Selection.Address(False, False)
    End Sub


    Now suppose you chose the range A1:B6, you have 6 rows of data, right? What about A5:C10? The same number of rows of data, only starting at a different row.

    MsgBox Selection.Rows.Count
    and its counterpart
    MsgBox Selection.Columns.Count

    will tell you how many rows/columns is contained within the selection.
    (I am assuming only one range is selected).

    Now, if you have 6 rows of data, the row below that is the row you want, but where does the first row start?

    Selection.Row will tell you the starting row of the data, no matter how many rows are used or wherever the range begins.

    Range("A5:A125").Row = 5
    Range("A5:IV65536").Row = 5, also

    Range("A100:A125").Row = 100

    So putting it together, with A5:B10 as a sample

    Selection.Row gives the first row in the range = 5

    Selection.Rows.Count gives the number of rows spanned by your selection = 6

    x = 5 + 6 = 11 and that is the next available row.

    You can definitely get there using numerous other ways, too. This was only one option available.

    HTH,
    Jay

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Rhode Island
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you for the explanation. I get the concepts, but I usually need it explained in English first! Thank you for your time and patience.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Rhode Island
    Posts
    141
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So, if x = rows counted, does y = columns counted? (If I only wanted to select the cells under my range?)


  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 12:26, LoriD wrote:
    So, if x = rows counted, does y = columns counted? (If I only wanted to select the cells under my range?)

    No. I only used x for illustration. You have to set your variable

    I could easily have used

    ROW_I_AM_TRYING_TO_FIND = .....

    You can use y, but you would have to tell it it's value

    y = Selection.Column + Selection.Columns.Count

    would find the column 1 to the right of your selection.

    Bye,
    Jay

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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