First row in unsorted range VBA

Question

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

If the user Selects a non-contiguous range, you'll need to iterate the Areas collection to get the lowest row number — chris neilsen, Oct 12 '22 at 22:16
Amend [THIS](https://stackoverflow.com/questions/37843753/last-row-or-cell-in-a-non-contiguous-range-not-a-worksheet-search) for first row. — Siddharth Rout, Oct 12 '22 at 22:22
@Siddharth Rout thanks for the hint. However this will only works for the last row. Count will not produce the first row. — Paul, Oct 12 '22 at 22:39

milo5m · Answer 1 · 2022-10-13T00:03:22.123

' 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.Count).Row 'CountLarge not needed
        For Each r In rng.Areas
            currMax = r.Rows(r.Rows.Count).Row
            If currMax > GetLastRow Then: GetLastRow = currMax
        Next r
    End If
End Function

For those interested in bitwise variant of previous code replace

If currMin < GetFirstRow Then: GetFirstRow = currMin with GetFirstRow = GetFirstRow Xor ((GetFirstRow Xor currMin) And --(GetFirstRow > currMin))

If currMax > GetLastRow Then: GetLastRow = currMax with GetLastRow = GetLastRow Xor ((GetLastRow Xor currMax) And --(GetLastRow < currMax))

score 0 · Answer 2 · answered Oct 12 '22 at 22:56

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

First row in unsorted range VBA

2 Answers2

Get Range First Row