CODEWITHSUNDEEP
excelvbarowrow-number
Paul
Paul

Reputation: 19

First row in unsorted range VBA

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

Upvotes: 1

Views: 307

Answers (2)

milo5m
milo5m

Reputation: 619

' 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))

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54853

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

Upvotes: 0

Related Questions