| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Macro
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging Macro
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 --------------- Jacob Skaria "FARAZ QURESHI" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging Macro
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" wrote: 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 --------------- Jacob Skaria "FARAZ QURESHI" wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Merging Macro
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 --------------- Jacob Skaria "FARAZ QURESHI" wrote: 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" wrote: 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 --------------- Jacob Skaria "FARAZ QURESHI" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing and merging Excel files by using a macro | Excel Discussion (Misc queries) | |||
| need urgent help on macro merging | Excel Discussion (Misc queries) | |||
| Help with Worksheet Merging Macro | New Users to Excel | |||
| Merging adjacent repeated columns with a macro | Excel Discussion (Misc queries) | |||
| Keep Format After Merging Worksheets Using CopyFromWorksheet Macro | Excel Discussion (Misc queries) | |||
Linear Mode
