Murray Brown
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Aikimark
I already converted those to functions like below......
Function to get first row in the selection:
Function to get last column used in the selection:
Test the Row Function:
Test the Column Function:
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
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
Test the Row Function:
Sub callRowfunction()
MsgBox getFirstRowInSelection
End Sub
Test the Column Function:
Sub CallColumnFunction()
MsgBox getLastColumnInSelection
End Sub
ASKER
ASKER
Thanks very much. That worked
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
ASKER
Thanks very much Subodh.
You're welcome! :)
Open in new window