Merge Cells in a Single Column Without Losing Data

[Bifack]

Hi. I have a small task that is similar to some of the other posting on merging, but not exactly the same. I'm trying to merge multiple cells in a single column (varying number of cells) into the first cell of that group without losing the data from the other cells. Here's an example:

Before:

___|__A__|__B__|__C__|
1__|__x1_|__y1_|_____|
2__|_____|__y2_|_____|
3__|_____|__y3_|_____|
4__|__x2_|__y4_|_____|
5__|__x3_|__y5_|_____|
6__|_____|__y6_|_____|

After:

___|__A__|__B__|__C__|
1..|..x1.|..y1.|.....|
...|.....|..y2.|.....|
___|_____|__y3_|_____|
2__|__x2_|__y4_|_____|
3..|..x3.|..y5.|.....|
___|_____|__y6_|_____|

*Ignore the "." - I can't get the editor to recognize multiple spaces

Is this possible? I want to create the line-breaks between the merged values, and I'd like it to know which values to merge based on which cells in Column A are populated. I REALLY need to learn some VBA...

Thanks,
Bif

 

[Damon Ostrander]

Hi Bifack,

Yes, this is possible. I assume you would like a macro that would for example merge rows 1-3 into 1 row when you select A1:C3 and run the macro (then you would do the same for 5-6--except that they would be 3-4 after merging 1-3).

I don't have time to provide this code at the moment, but if I get time I will supply it tomorrow unless someone else beats me to it.

Damon

 

[Bifack]

That's exactly what I'm trying to do! As the cells are merged, the leftover blank rows (e.g. 2 and 3 in the example) would be deleted. Since I can always go back and filter out all the blank rows, that's not as big of a deal. However, I don't want B1-B3 to merge into one cell, but that one cell span three rows. Make sense?

Is it possible to take it a step further and either:
A. have the macro check for data in column A and merge all the data in column B until the next non-NULL value is found in column A? Then the macro could run through the entire spreadsheet all at once.
B. if option A can't happen, can the macro be tied to a button or hot-key so that it can be run without using the menus? If it's not obvious, I'm a macro newbie.

My current file has close to 1000 rows, so finding a way to ease this process is ideal.

Thanks,
Bif

 

[Damon Ostrander]

Hi again Bif,

Please let me know if this does what you want. It only merges column B since you didn't mention anything about other columns beyond that--I hope this is what you intended.

Sub MergeA()
'Merge cells in col B based on empty cells in col A
Dim iRow As Long
Dim mRow As Long
For iRow = 2 To [b65536].End(xlUp).Row
If IsEmpty(Cells(iRow, 1)) Then
'take value in column B and merge with value in row above
mRow = Cells(iRow, 1).End(xlUp).Row
Cells(mRow, 2) = Cells(mRow, 2) & vbLf & Cells(iRow, 2)
End If
Next iRow

'Delete empty rows
For iRow = [b65536].End(xlUp).Row To 2 Step -1
If IsEmpty(Cells(iRow, 1)) Then Rows(iRow).Delete
Next iRow

'align col A cells to top
Columns(1).VerticalAlignment = xlTop
End Sub


This macro process the entire the entire column in one step.

 

[Bifack]

Damon,
You rock! ray: That works perfect! I guess I understand VBA a little better than I originally thought, because your code actually made sense to me once I studied it for a little while. I owe you a case of beer as you just saved me about 2 days of manual labor in the Excel!

 

[Damon Ostrander]

Hi Bif,

Can you just email it (the beer)?

Damon