Working With Excel Area Selection In VBA

In a recent project of mine, I needed to allow the users to make a selection within their worksheet and then process the data contained within.

I dusted off some older projects I had done to extract a couple little procedures I thought I’d share as they can be useful when dealing with this type of scenario.  They’re all used to determine the boundaries of the selected area and the location of the ‘ActiveCell’.
 

The ActiveCell

'---------------------------------------------------------------------------------------
' Procedure : GetActiveColumn
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the currently select cell's column
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References: http://support.microsoft.com/default.aspx?scid=kb;en-us;153318
'
' Usage:
' ~~~~~~
' GetActiveColumn()
'   Returns -> E
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         ??????????              Initial Release
' 2         2024-11-28              Added Procedure Header and Error Handler
'---------------------------------------------------------------------------------------
Public Function GetActiveColumn() As String
On Error GoTo Error_Handler
    Dim sActiveCellAddress    As String

    sActiveCellAddress = ActiveCell.Address
    GetActiveColumn = Mid(sActiveCellAddress, InStr(sActiveCellAddress, "$") + 1, InStr(2, sActiveCellAddress, "$") - 2)
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetActiveColumn" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetActiveRow
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the currently select cell's row
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' GetActiveRow
'   Returns -> 528
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         ??????????              Initial Release
' 2         2024-11-28              Added Procedure Header and Error Handler
'---------------------------------------------------------------------------------------
Public Function GetActiveRow() As String
On Error GoTo Error_Handler
    Dim sActiveCellAddress    As String

    sActiveCellAddress = ActiveCell.Address
    GetActiveRow = Mid(sActiveCellAddress, InStrRev(sActiveCellAddress, "$") + 1)
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetActiveRow" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

The Selection (Area)

'---------------------------------------------------------------------------------------
' Procedure : GetFirstRowInSelection
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the first row in the selected range
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' GetFirstRowInSelection
'   Returns -> 523
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         ??????????              Initial Release
' 2         2024-11-28              Added Procedure Header and Error Handler
'---------------------------------------------------------------------------------------
Public Function GetFirstRowInSelection() As Long
On Error GoTo Error_Handler

    GetFirstRowInSelection = Selection.Row
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetFirstRowInSelection" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetLastRowInSelection
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the last row in the selected range
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' GetLastRowInSelection
'   Returns -> 533
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         ??????????              Initial Release
' 2         2024-11-28              Added Procedure Header and Error Handler
'---------------------------------------------------------------------------------------
Public Function GetLastRowInSelection() As Long
On Error GoTo Error_Handler
    Dim selectedRange         As Range
    
    Set selectedRange = Selection
    GetLastRowInSelection = selectedRange.Row + selectedRange.Rows.Count - 1
    
Error_Handler_Exit:
    On Error Resume Next
    Set selectedRange = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetLastRowInSelection" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetFirstColumnInSelection
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the first column in the selected range
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' GetFirstColumnInSelection
'   Returns -> 5
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         ??????????              Initial Release
' 2         2024-11-28              Added Procedure Header and Error Handler
'---------------------------------------------------------------------------------------
Public Function GetFirstColumnInSelection() As Long
On Error GoTo Error_Handler

    GetFirstColumnInSelection = Selection.Column
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetFirstColumnInSelection" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetLastColumnInSelection
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine the last column in the selected range
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' GetLastColumnInSelection
'   Returns -> 9
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         ??????????              Initial Release
' 2         2024-11-28              Added Procedure Header and Error Handler
'---------------------------------------------------------------------------------------
Public Function GetLastColumnInSelection() As Long
On Error GoTo Error_Handler
    Dim selectedRange         As Range
    
    Set selectedRange = Selection
    GetLastColumnInSelection = selectedRange.Column + selectedRange.Columns.Count - 1
    
Error_Handler_Exit:
    On Error Resume Next
    Set selectedRange = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: GetLastColumnInSelection" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

One response on “Working With Excel Area Selection In VBA”