First row in unsorted range VBA-CodePudding

I ask the user to select cells with an InputBox. I would then like to find the cell with the lowest row number in that range. Is there some function that does it or do I have to iterate through all cells? If the user selects from top to bottom there is no problem. But if the selected range looks like this "A10;A200;A1", I will get row 10 as result of my code below instead of row 1.

Set Rng = Application.InputBox( _
      Title:="Selection", _
      Prompt:="Please select cells", _
      Type:=8)
Myfirstrow  = Rng(1).Row

CodePudding user response：

Get Range First Row

Option Explicit


Sub Test()
    
    Dim rng As Range: Set rng = Application.InputBox( _
        Title:="Selection", _
        Prompt:="Please select cells", _
        Type:=8)
    
    Dim MyFirstRow As Long: MyFirstRow = GetRangeFirstRow(rng)
    
    MsgBox "My first row is " & MyFirstRow & ".", vbInformation
    
End Sub


Function GetRangeFirstRow(ByVal rg As Range) As Long
    
    GetRangeFirstRow = rg.Worksheet.Rows.Count   1
    
    Dim arg As Range
    
    For Each arg In rg.Areas
        If arg.Row < GetRangeFirstRow Then GetRangeFirstRow = arg.Row
    Next arg

End Function

CodePudding user response：

' Returns 0 if rng is nothing
Function GetFirstRow(rng As Range) As Long
    Dim r As Range
    Dim currMin As Long
    
    If rng Is Nothing Then
        GetFirstRow = 0
    Else
        GetFirstRow = rng.Row
        For Each r In rng.Areas
            currMin = r.Row
            If currMin < GetFirstRow Then: GetFirstRow = currMin
        Next r
    End If
End Function

' Returns 0 if rng is nothing
Function GetLastRow(rng As Range) As Long
    Dim r As Range
    Dim currMax As Long
    
    If rng Is Nothing Then
        GetLastRow = 0
    Else
        GetLastRow = rng.Rows(rng.Rows.CountLarge).Row
        For Each r In rng.Areas
            currMax = r.Rows(r.Rows.CountLarge).Row
            If currMax > GetLastRow Then: GetLastRow = currMax
        Next r
    End If
End Function