Merging Macro

[FARAZ QURESHI]

I have a list with some blank cells in between. I need to have the entries
appearing at top be merged with the blank cells (if any) below. For example
the table:

BRANCH 1 PRODUCT 1
PRODUCT 2
PRODUCT 7
PRODUCT 3
BRANCH 2 PRODUCT 1
PRODUCT 2
PRODUCT 6
PRODUCT 3
BRANCH 3 PRODUCT 4
BRANCH 4 PRODUCT 5
BRANCH 5 PRODUCT 1
PRODUCT 2
PRODUCT 6
PRODUCT 3

be redesigned as with the branch names to be merged only against their
relative PRODUCTS. Do NOTE that Branch 3 & 4 have no empty cell beneath it
and consist only one product.

 

[Jacob Skaria]

Assuming you have data from row1. onwards in .ColA and ColB without headers
try the below macro and feedback

Sub Macro1()

Dim lngRow As Long
Dim lngLastRow As Long
Dim lngTempRow As Long

lngTempRow = 1

lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = 1 To lngLastRow
If Range("A" & lngRow) <> "" Then
If lngRow > 1 And lngTempRow <> lngRow - 1 Then
Range("A" & lngTempRow & ":A" & lngRow - 1).Merge
Range("A" & lngTempRow & ":A" & lngRow - 1).VerticalAlignment = xlCenter
End If
lngTempRow = lngRow
End If
Next
If lngRow > 1 And lngTempRow <> lngRow - 1 Then
Range("A" & lngTempRow & ":A" & lngRow - 1).Merge
Range("A" & lngTempRow & ":A" & lngRow - 1).VerticalAlignment = xlCenter
End If


End Sub


If this post helps click Yes

 

[FARAZ QURESHI]

Once again!
XClent Jacob!

However, can you help me in devising a code so as to have it worked on any
specific cells selected instead of only limited for Column A?

 

[Jacob Skaria]

In the below code change all "A" to the required column..Change "B" to column
where you have the continous list...

If this post helps click Yes