Concatenate all populated cell addresses in range

jedwardo

Board Regular
Joined
Aug 21, 2012
Messages
122
Thought this one would be easy, not getting any results or errors and not sure where to go from here. Most of what I can find on concatenating doesn't concern cell addresses. Trying to get all the populated cell addresses in range D3:GE50 together in a single cell (Sheet2.Range("A2"), after which I want to copy those ranges from Sheet1 to the same ranges on Sheet2 but I can't seem to get over this first hurdle.

Code:
Private Sub CommandButtonExtractRefs_Click()


    Dim x As String


        For Each Cell In Range("D3:GE50")
            If Cell.Value <> "" Then
                x = x & Cell.Address & ","
        Next Cell
    Sheet2.Range("A2") = x
    
End Sub

Regards,
Jordan
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I did it as a loop and it worked. Maybe try the below:

Code:
Sub macro()

Application.ScreenUpdating = False


a = 3
endcol = 187
endrow = 50


Do While a < endcol + 1
    b = 4
    Do While b < endrow + 1
        If Cells(a, b) <> "" Then
            x = x & Cells(a, b).Value & ","
        End If
    b = b + 1
    Loop
a = a + 1
Loop
    
Sheet2.Range("A2") = x
    
Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Awesome, that does it. It continues concatenating past row 50 but I can make sure that space stays empty before this runs
 
Upvote 0
Awesome, that does it. It continues concatenating past row 50 but I can make sure that space stays empty before this runs

Sorry I had the endrow and endcol around the wrong way, try this. The macro will now run to row 50 and column 187 (GE), whereas before it would run to row 187 and column 50.

Code:
Sub macro()

Application.ScreenUpdating = False


a = 3
endcol = 187
endrow = 50


Do While a < endrow + 1
    b = 4
    Do While b < endcol + 1
        If Cells(a, b) <> "" Then
            x = x & Cells(a, b).Value & ","
        End If
    b = b + 1
    Loop
a = a + 1
Loop
    
Sheet2.Range("A2") = x
    
Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top