Range select to single select on same range

Copper Contributor

Excel Selection Example.png

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 I can't figure out how to change my range selection to a collection of individual selections over that same range.


I am trying to do this so I can insert a row on every TRUE value in a column. Using Find / Replace, I can select all the TRUE values, but when I right click and Insert Entire Row, adjacent TRUE values inserts multiple rows above the first TRUE instead of a row between each selected value. If I were to spend a very long time manually ctrl clicking each cell, the insert would work how I want it to, but that is an extremely inefficient use of time.

1 Reply
best response confirmed by ForceofNature (Copper Contributor)


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)
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.