Merging cells in a column to represent the value of another column

Hello wonderful peoples,

I am having trouble to implement something. Basically what I am doing is creating a layout. From the dropdown menu, I have different options in column B, and these options have different sizes, as found in column E.The data from the dropdown is automatically pulled from another tab in the same sheet. What I want now is when the value of column E is X, i want X cells in column B to be automatically merged. For example suppose if I choose cell B30, and the value in E30=2, I want cells B29 and B30 to merge together. Is it possible to do it with a formula (IF statement) or need to write a vba code (I am quite poor at writing codes). Thank you in advance for your help.
excel.jpg

I have attached a sample of the workbook I want to modify. As seen from the desired output picture, when selecting from the dropdown, I want the cells to merge according to the U number. For example if I select Y from the dropdown on cell 30, the U=4. So I want cells 27-30 to merge.
Thanks.

Anyone to help?

Hi !

Try this demonstration :

PHP Code: 

Sub Demo4Evil()
         Dim Rg As Range
    For Each Rg In ActiveSheet.UsedRange.Columns(5).SpecialCells(xlCellTypeFormulas, xlNumbers)
          If Rg.Value > 1 Then Rg(2 - Rg.Value, -2).Resize(Rg.Value).Merge
    Next
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

doesn't work

Hi, it is imperative that I merge these cells because the value in column E represents the size of the chassis

Originally Posted by tabascorez

doesn't work

As my code rocks on my side with your attachment ‼

can you repost the attachment with your code?

OK, I got the macro to run, but I have to run it each time when I make a change. Is there a way to make it run automatically when I change from the drop-down?

So Use Worksheet_Change event within worksheet module …

do you mean this step?
Copy the code that you want to use
Select the worksheet in which you the code to run
Right click on the sheet tab and click View Code, to open the Visual Basic Editor.

Yes paste the code into worksheet module within Worksheet_Change procedure …

Hi Marc, sorry for late reply,was super busy. I tried to run it as you mentioned, but no good results