Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Given a user-selected Range, what is the simplest way to determine the starting row number, and ending row number?

Range.Address returns a string containing any number of possible formats. There has to be something simpler.


Dim oRange As Range
Dim startRow As Integer
Dim endRow As Integer

oRange = Range("A1:X50")
startRow = oRange.Address.StartRow
endRow = oRange.Address.EndRow

of course, those properties do not exist. I want to do the same to return column letters.

share|improve this question
Don't forget that you can have multiple ranges selected very easily by the user. – Nissan Fan Apr 21 '10 at 19:42
True, but I would not allow a sort if more than one range was selected. Just as if multiple ranges are selected in Excel, and you click the Sort button, Excel throws an error. Something like, if necessary: If oRange.Areas.Count > 1 Then MsgBox "Sort cannot be applied to multiple selected areas." End If – Robert Kerr Apr 21 '10 at 19:49
up vote 1 down vote accepted

how about:

Dim oRange As Range
Dim startRow As Integer
Dim endRow As Integer

With Selection
    startRow = .Rows(1).Row
    endRow = .Rows(Selection.Rows.Count).Row
End With
share|improve this answer
Dude! Thanks Sorry I cannot +1 you yet. – Robert Kerr Apr 22 '10 at 1:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .