merging cells and eliminating spaces for empty cells

  • Thread starter Thread starter Jill
  • Start date Start date
J

Jill

I want to merge the text from about 25 columns into one column. It is a
course list so each registrant has a different combination of courses. Is
there a way to do this and not have a bunch of extra spaces wherever someone
does not have a course?
 
Hi Jill

I am assuming that column A contains the name of the person, and columns
B to Z contain the 25 potential courses.

This code will create a list in column AA of the sheet

Sub CreateList()
Dim i As Long, j As Long, lr As Long, list As String
Dim ws As Worksheet
Set ws = ActiveSheet
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lr
For j = 2 To 26
If ws.Cells(i, j).Value <> "" Then
If list = "" Then
list = ws.Cells(i, j).Value
Else
list = list & " ," & ws.Cells(i, j).Value
End If
End If
Next j
ws.Cells(i, "AA") = list
list = ""
Next i
End Sub

To Install
Copy code
Alt+F11 to invoke VB Editor
Alt+I+M to Insert Module
Paste code into White pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up macro list
Select macro name
Run
 
You could use a User Defined Function and enter a formula.

Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(a1:a10,"|") desired delimiter between quotes
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text <> "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange22 = Left(sbuf, Len(sbuf) - Len(Delim))
End Function

This goes into a General Module.

See Roger's post for how-tos.


Gord Dibben MS Excel MVP
 
Back
Top