Cobining various numbers of cells without using the & or concatena

[Ken Peterson]

I have over 16,000 rows of data. In the first column I need to combine
cosecutive cells. The number of cells to be combined varies. For example, I
may need to combine cells a1, a1, a3, a4 in one instance; cells a100, a101 in
another instance; cells a5200:a5220 in another instance; depending on the
cells in the other columns. The combined cells need to be comma-delimited.

I know I can use =A1&" "&A2 or =concatenate(a1,a2) or =concatenate
(a5200:a5220), etc. However, this is tedious because I have to manually do
this throughout all 16,000 rows.

What I would like to do is highlight the cells that need to be combined and
use a keystroke or two to combine them.

For example,

The first instance of cells to be combined is:
cell a1 = 1245-aw
cell a2 = 5241-ed
cell a3 = 3464-th
cell a4 = 156302-yu

After using the keystrokes, I want the result in cell a1 to look like
"1245aw, 5241-ed, 3464-th, 156302-yu" (without the quotes). There will be
between 2 and 20 cells to be combined, numerous times throughout the
worksheet. Right now I have use cut and paste for every instance to be
combined.

Thank you for your anticipated help.

 

[Ken Peterson]

Hi Bernie,

Thank you very much, especially for the very fast response. That is exactly
what I was looking for. I would delete the values in cells a2:a4 and then
copy the value in a1 to cells a2:a4. If you could add the ability to delete
cells a2:a4 (or whatever was selected) after the combination occurs that
would be very helpful.

The reason I need to do this is that once the cells are combined I need to
subsequently copy that cell (a1) to a number of adjacent cells in the same
column (the number of cells may or may not be equal to the number of
combined cells). This is so we can see how many records each client has in
the rows for that client. Each clint may have different information in
various columns on each row.

Thanks again,
Ken

 

[Bernie Deitrick]

Ken,

This should clear the other cells:

Sub CombCell2()
Dim i As Integer
Dim myS As String
Application.EnableEvents = False
myS = Selection.Cells(1).Value
For i = 2 To Selection.Cells.Count
myS = myS & ", " & Selection.Cells(i).Value
Selection.Cells(i).ClearContents
Next i
Selection.Cells(1).Value = myS
Application.EnableEvents = True
End Sub

But if there is information in another column that you use to decide what cells to combine, the
macro could be rewritten to choose the cells automatically instead of you doing it manually - the
whole column could be done in a matter of seconds.

Try to describe your basis of choosing cells....

HTH,
Bernie
MS Excel MVP