How to determine if the user has selected more than one column?

[JR1412]

I would like to ensure that the user has only selected cells in one column before launching the macro shown below. Is there an easy way to determine if the range is more than one column?

    Dim strCell As String
    Dim c As Range
    Dim rng As Range
    '
    Set rng = Selection.Cells
    For Each c In rng
        strCell = c.Value
        Call RegExp(strCell)
        c.Value = strCell
    Next c

 

[Marcelo Branco]

Maybe inserting this as the first code-line

If Selection.Columns.Count > 1 Then Exit Sub

HTH

M.

 

[JR1412]

Thank you very much.

 

[Marcelo Branco]

Thank you very much.

You are welcome and tks for the feedback

M.

 

[brettdj]

You could add suggest the range to the user (of the first column of the active selection only) as a default, which gives them the chance to accept the default, change the range or exit the code

You may also want to consider using a variant array to run what appears to be a regular expression sub over the range

Cheers

Dave

Sub UserPrompt()
    Dim rng1 As Range
    On Error Resume Next
    Set rng1 = Application.InputBox("Please select range to work on", , Intersect(Selection, Selection.Columns(1)).Address, , , , , 8)
    On Error GoTo 0
    If rng1 Is Nothing Then Exit Sub
    For Each c In rng1
        strCell = c.Value
        Call RegExp(strCell)
        c.Value = strCell
    Next c
    MsgBox "Done"
End Sub