Go to row under selected region?

[LoriD]

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)

 

[Jay Petrulis]

Hi,

This should get you started

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

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

End Sub
'------

Bye,
Jay

 

[LoriD]

Thanks, Jay.

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

Lori

 

[Jay Petrulis]

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

 

[LoriD]

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.

 

[LoriD]

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

 

[Jay Petrulis]

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