VBA Excel SelectionChange Reading Target Range into array and returning column count-CodePudding

When a new selection of cells is made, I want to convert the range into an array for later use, and determine the number of columns selected. This is the code I have, but I get a type mismatch error. Can someone point me in the right direction?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim arr As Variant
    Dim colcount As Long
    arr = Target
    colcount = UBound(arr, 2)
    Debug.Print colcount
End Sub

CodePudding user response：

The error is due to Range.Value returning only a single value when the range only has a single cell.

If Target is a single cell, then Target will return a single value instead of an array. arr will be a single value and UBound(arr, 2) will error because arr is not an array.

You can use IsArray to check if arr is an array or not before attempting UBound. Also, if you intend to save these values for future use, you should define them as Module-Level variables.

Dim arr As Variant
Dim colcount As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    arr = Target
    If IsArray(arr) Then
        colcount = UBound(arr, 2)
    Else
        colcount = 1
    End If
    Debug.Print colcount
End Sub

To fill colcount, other than UBound, you could also just do colcount = Target.Columns.Count which would also avoid needing to check IsArray.