Merging Macro

F

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.
 
J

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
 
F

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?
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top