Merge empty cells until the next filled cells downwards

B

Baydoun

Board Regular
Joined
Apr 6, 2016
Messages
134
i need to merge the blank the cells together until they reach a filled cell , as in the example below ,
i need to merge the B2 within the blank cells up to B19 , the merge B20 with B21 .

ALSO merge the A2 up to A19 and then A20 with A21 etc...
looking forward to your help gratefully , thanks
[TABLE="width: 1064"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="2" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]#[/TD]
[TD="align: center"]BRAND[/TD]
[TD="align: center"]MODEL[/TD]
[TD="align: center"]FAMILY[/TD]
[TD="align: center"]CURRENT PRICE[/TD]
[TD="align: center"]BECOME[/TD]
[TD="align: center"]DISCOUNT[/TD]
[TD="align: center"]NOTES[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SILVIAN[/TD]
[TD="align: center"]BLOUSE[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]CARDIGAN[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]DRESS[/TD]
[TD="align: center"]KIDS[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]DRESS[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]JACKET[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]JEANS[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]JOGGING[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]LEGGING[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]PANT[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]SHIRT[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]SHORT[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]SINGLET[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]SKIRT[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]SWEATER[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]SWEATER[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]TOP[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]T-SHIRT[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]VEST[/TD]
[TD="align: center"]WOMEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]DIESEL[/TD]
[TD="align: center"]SHIRT[/TD]
[TD="align: center"]MEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]T-SHIRT[/TD]
[TD="align: center"]MEN[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]EXCEPTION : [/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
thank you so much ,
the first VBA is the best ,
it's working like a charm only i had to create an additional column at first as it has been done to work on the columns B & C
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I have to agree with Jonmo if you are planning to do a lot more coding. If the code is only to merge the cells and you are not planning further automation then merging is fine. Merged cells absolutely suck when manipulating data especially with code. If the project requires merging of cells then I make certain it is the last portion of code for the sheet.
 
Upvote 0
Baydoun said:
thank you so much ,
the first VBA is the best ,
it's working like a charm only i had to create an additional column at first as it has been done to work on the columns B & C
Click to expand...

just change the column references from the numbers 2 and/or 3 to the column numbers that you need. Any code that you get as a solution to your problem should always be taken with the understanding that you may have to make changes to fit your data. You should never change your data to fit code.
 
Upvote 0
i need the merging to print the data and make more understandable and beautiful to the viewers ,
thank you so much for the help and concern
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
232
frewert
F
A
  • Question Question
copy data from multiple sheets based on headers into another sheet
Replies
3
Views
405
alansidman
A
D
  • Question Question
Fill out columns based on reference table
Replies
4
Views
518
Trixterz
T
P
  • Solved
Need to calculate Average of first 5 non-zero cells in a ROW
2 3
Replies
22
Views
2K
Eric W
Eric W
K
  • Question Question
Conditional formatting each next step
Replies
3
Views
982
Fluff
Fluff

Forum statistics

Threads
1,224,562
Messages
6,179,526
Members
452,923
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top