Merging Macro

  • Thread starter Thread starter FARAZ QURESHI
  • Start date Start date
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.
 
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
 
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?
 
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
 
Back
Top