Get the address of active cells into array

[amalghoshav]

Hi,

Can anyone help with a way to get the address of active cells into an array. There can be 2 or more active cells (selected with Ctrl pressed or a range like A1:C5 etc)..

Thanks in advance

Amal

 

[stanleydgromjr]

amalghoshav,

Welcome to the MrExcel Board.

After you make a "Selection" of cells, then run the below macro, and it will load the array with the cell addresses.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Option Base 1
Sub FillArray()
    Dim MyArray As Variant
    Dim MyCount&
    Dim c As Range
    MyCount& = Selection.Count
    ReDim MyArray(MyCount&)
    MyCount& = 1
    With Selection
        For Each c In Selection
            MyArray(MyCount&) = c.Address
            'Debug.Print MyArray(MyCount&)
            MyCount = MyCount + 1
        Next
    End With
End Sub

Make a selection of cells and run the "FillArray" macro.


Have a great day,
Stan

 

[gauntletxg]

I'm not sure if it can be done with one line of code as you can with cell values. I played around with it for a little bit and couldn't figure it out. But it can be done with a loop

Sub SelToArray()
Dim cell As Range, SelArray As Variant, i As Integer
If Selection.Count = 1 Then Exit Sub
ReDim SelArray(1 To Selection.Count)

i = 1
For Each cell In Selection
    SelArray(i) = cell.Address
    i = i + 1
Next cell
End Sub

 

[amalghoshav]

Hi Stanleydgromjr

Thanks a lot. Its working great and to the point.
Appreciate you efforts

Amal

 

[amalghoshav]

Hi gauntletxg

Thanks a lot for the reply..
Appreciate the efforts

Amal