asked on

Excel VBA Find Lowest Row number in any selection

Hi
I was given the following Excel VBA code (by SubodH) to find the first column in any selection.
What similar code would I use to find the first Row of any selection?
Thanks

Sub FirstColumnInSelection()
Dim Rng As Range, Cell As Range, Col As Long
If Selection.Areas.Count = 1 Then
    MsgBox ActiveCell.Column
    MsgBox ActiveCell.Address
Else
    Col = Selection.Areas(Selection.Areas.Count).Column
    For Each Rng In Selection.Areas
        If Rng.Column <= Col Then
            Col = Rng.Column
            Set Cell = Rng.Cells(1).Offset(Rng.Rows.Count - 1)
        End If
    Next Rng
    MsgBox Cell.Column
    MsgBox Cell.Address(0, 0)
End If
End Sub

Open in new window

Subodh Tiwari (Neeraj)

Maybe this.....

Sub FirstRowInSelection()
Dim Rng As Range, Cell As Range, RW As Long
If Selection.Areas.Count = 1 Then
    MsgBox ActiveCell.Row
    MsgBox ActiveCell.Address
Else
    RW = Selection.Areas(Selection.Areas.Count).Row
    For Each Rng In Selection.Areas
        If Rng.Row <= RW Then
            RW = Rng.Row
            Set Cell = Rng.Cells(1).Offset(Rng.Rows.Count - 1)
        End If
    Next Rng
    MsgBox Cell.Row
    MsgBox Cell.Address(0, 0)
End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

@Aikimark

I already converted those to functions like below......

Function to get first row in the selection:

Function getFirstRowInSelection()
Dim Rng As Range, Cell As Range, RW As Long
If Selection.Areas.Count = 1 Then
    getFirstRowInSelection = ActiveCell.Row
Else
    RW = Selection.Areas(Selection.Areas.Count).Row
    For Each Rng In Selection.Areas
        If Rng.Row <= RW Then
            RW = Rng.Row
            Set Cell = Rng.Cells(1).Offset(Rng.Rows.Count - 1)
        End If
    Next Rng
    getFirstRowInSelection = Cell.Row
End If
End Function

Open in new window

Function to get last column used in the selection:

Function getLastColumnInSelection()
Dim Rng As Range, Cell As Range, Col As Long
If Selection.Areas.Count = 1 Then
    getLastColumnInSelection = ActiveCell.Offset(Selection.Rows.Count - 1, Selection.Columns.Count - 1).Column
Else
    For Each Rng In Selection.Areas
        If Rng.Column > Col Then
            Col = Rng.Column
            Set Cell = Rng.Cells(1).Offset(Rng.Rows.Count - 1)
        End If
    Next Rng
    getLastColumnInSelection = Cell.Column
End If
End Function

Open in new window

Test the Row Function:

Sub callRowfunction()
MsgBox getFirstRowInSelection
End Sub

Open in new window

Test the Column Function:

Sub CallColumnFunction()
MsgBox getLastColumnInSelection
End Sub

Open in new window

Murray Brown

ASKER

Thanks SubodH but running your code on the selection shown in the image returned the wrong result. I appreciate the help though. Thanks

Murray Brown

ASKER

Thanks very much. That worked

Subodh Tiwari (Neeraj)

Okay then you can try this....

Function getFirstRowInSelection()
Dim Rng As Range, Cell As Range, RW As Long
If Selection.Areas.Count = 1 Then
    getFirstRowInSelection = ActiveCell.Row
Else
    RW = Selection.Areas(Selection.Areas.Count).Row
    For Each Rng In Selection.Areas
        If Rng.Row <= RW Then
            RW = Rng.Row
            Set Cell = Rng.Cells(1)
        End If
    Next Rng
    getFirstRowInSelection = Cell.Row
End If
End Function

Open in new window

Murray Brown

ASKER

Thanks very much Subodh.

Subodh Tiwari (Neeraj)

You're welcome! :)