Concatenate all populated cell addresses in range

[jedwardo]

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.

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

 

[Jeckford]

I did it as a loop and it worked. Maybe try the below:

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

 

[jedwardo]

Awesome, that does it. It continues concatenating past row 50 but I can make sure that space stays empty before this runs

 

[Jeckford]

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.

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

 

[jedwardo]

Works perfect, thanks alot Jeckford.