Forum Discussion

ForceofNature's avatar
ForceofNature
Copper Contributor
Jun 20, 2023

Range select to single select on same range

  I would like to drag select a range and then convert the selection as if I had ctrl clicked each cell individually. I tried using Alt + ; which does a similar thing on a filtered table, but ...
Excel
  • HansVogelaar's avatar
    HansVogelaar
    Jun 20, 2023

    ForceofNature 

    You might create the following macro in a module in the Visual Basic Editor:

    Sub ConvertSelection()
    Dim s As String
    Dim cel As Range
    For Each cel In Selection
        s = s & "," & cel.Address(False, False)
    Next cel
    s = Mid(s, 2)
    Range(s).Select
End Sub

    If you create it in your personal macro workbook PERSONAL.XLSB, it will be available in all open workbooks. You can assign it to a custom keyboard shortcut and/or custom Quick Access Toolbar button, if you like.

HansVogelaar's avatar
HansVogelaar
MVP
Jun 20, 2023

ForceofNature 

You might create the following macro in a module in the Visual Basic Editor:

Sub ConvertSelection()
    Dim s As String
    Dim cel As Range
    For Each cel In Selection
        s = s & "," & cel.Address(False, False)
    Next cel
    s = Mid(s, 2)
    Range(s).Select
End Sub

If you create it in your personal macro workbook PERSONAL.XLSB, it will be available in all open workbooks. You can assign it to a custom keyboard shortcut and/or custom Quick Access Toolbar button, if you like.

Resources