Adding names from different cells to a consolidated list in one cell

**gaia** · 06-18-2012, 08:02 AM

As the topic describes I need to add names from different cells to a consolidated list in one single cell. This could be done easily with the "Concatenate" function. The problem is that the names are spread out over different cells in a spreadsheet.

The combining key factor is that all name end with /ABC.

So what I am trying to do is to write a piece of VBA code that searches all cells in a spreadsheet (or just from A1-Z200 would be more than enough), and makes a single text string in a single cell out of all values ending with /ABC.

Hope you can help.

**tom1977** · 06-18-2012, 08:11 AM

Hi check this
Sub aa()

With Worksheets(1).Range("a2:z200")
    Set c = .Find("/ABC", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            [a1] = [a1] & " " & c
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

End Sub

Adding names from different cells to a consolidated list in one cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding names from different cells to a consolidated list in one cell

Re: Adding names from different cells to a consolidated list in one cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions