Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

Desired:

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.