How to refer to a selected block of cells in vba excel

Question

How to refer in the code to the selected range of cells in the active sheet. To clarify, I am not asking how to select a range of cell.

Is there any ActiveRange ActiveRegion or something? I haven't found it...

For example, (not working) here is a simple sub:

Sub test()
    Dim region, cel As Range
    
    region = ActiveCell.CurrentRegion
    For Each cel In region
        cel = 1
    Next cel
    
End Sub

this might be of more help than my previous comment: https://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object — Luuk, Dec 24 '20 at 15:52
There are some more answers in this thread that may help you. https://stackoverflow.com/questions/4080741/get-user-selected-range — Archie Adams, Dec 24 '20 at 17:19

score 0 · Answer 1 · answered Dec 24 '20 at 15:57

A couple of remarks: Define all the variables types and Selection is already a Range if a Range is selected. Beware that it may return other objects, so you should check the type of object returned before working with it

Read this

Public Sub LoopActiveRange()

    Dim sourceCell As Range

    For Each sourceCell In Selection
        sourceCell = 1
    Next sourceCell

End Sub

score 0 · Answer 2 · answered Dec 25 '20 at 01:01

To work with Selected cells use Selection property (Excel) MSDN

Example

Option Explicit
Public Sub Example()
    Dim rng As Range
    
    For Each rng In Selection
        DoEvents
        rng.Value = "value"  ' adds a to all selected cells
    Next

End Sub

Remarks
_{The returned object type depends on the current selection (for example, if a cell is selected, this property returns a Range object). The Selection property returns Nothing if nothing is selected.}

How to refer to a selected block of cells in vba excel

2 Answers2