VB to copy column then paste as a new row with merged cells

[learn2power]

I am trying to copy a column value and paste as a new row after each row as a merged cell. I have started my step towards this and managed to add a blank row after each row on specified sheet with range but need further on copying the column cell value and paste into each row...

I hope the logic makes sense for all.

Any help would be appreciated.

Thanks

 Sub Macro11()
 Dim LastRow, RowNumber As Long
 Dim ws As Worksheet
 'FOLLOWING WORKS WITH RANGED AND SPECIFIED SPREADSHEET
 Set ws = ThisWorkbook.Worksheets("Asset")
With ws
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      For RowNumber = LastRow To 11 Step -1 'FROM WHAT ROW TO START THE ROW BLANK
    .Rows(RowNumber).Insert
Next RowNumber
End With
End Sub

 

[Joe4]

Welcome to the Board!

Do you want to make the changes directly to the range, or make a copy of it elsewhere?
If so, exactly where does the data reside (addresses) and where do you want the new copy?
Also, do the cells really need to be merged cells, or can they just be centered across the selection?

The "center across selection" formatting option gives you the same visual effect, but without all the issues that merged cells cause
(merged cells are VERY, VERY BAD - the cause all sorts of issues for things like sorting and VBA).
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[learn2power]

Welcome to the Board!

Do you want to make the changes directly to the range, or make a copy of it elsewhere?
If so, exactly where does the data reside (addresses) and where do you want the new copy?
Also, do the cells really need to be merged cells, or can they just be centered across the selection?

The "center across selection" formatting option gives you the same visual effect, but without all the issues that merged cells cause
(merged cells are VERY, VERY BAD - the cause all sorts of issues for things like sorting and VBA).
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Thanks @Joe4. I have figured out a way to add a blank row with merged cells. I understood about auto row height issue in excel. But I am thinking to use vb to increase row height depends on the characters of a row.

Dim var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, var11, 
var12, var13, var14, var15, var16, var17, var18, var19, var20, var21, 
var22, var23, var24, var25, var26 As Variant
var1 = ThisWorkbook.Worksheets("VariablSheet").Range("F4").Value
var2 = ThisWorkbook.Worksheets("VariablSheet").Range("F5").Value....
            If Len(.Range("A" & RowNumber + 1).Value) > var6 Then 'TS Added to adjust row height
                    .Rows(RowNumber + 1).RowHeight = 75 ' TS 6 Line
            ElseIf Len(.Range("A" & RowNumber + 1).Value) > var5 Then 'TS Added to adjust row height
                    .Rows(RowNumber + 1).RowHeight = 65 ' TS 5 Line
            ElseIf Len(.Range("A" & RowNumber + 1).Value) > var4 Then 'TS Added to adjust row height
                    .Rows(RowNumber + 1).RowHeight = 52 ' TS 4 Line
            ElseIf Len(.Range("A" & RowNumber + 1).Value) > var3 Then 'TS Added to adjust row height
                    .Rows(RowNumber + 1).RowHeight = 39 ' TS 3 Line
            ElseIf Len(.Range("A" & RowNumber + 1).Value) > var2 Then 'TS Added to adjust row height
                    .Rows(RowNumber + 1).RowHeight = 26 ' TS 2 Line
            ElseIf Len(.Range("A" & RowNumber + 1).Value) > var1 Then  'TS Added to adjust row height
                    .Rows(RowNumber + 1).RowHeight = 15 ' TS Single Line

 

[learn2power]

Is there a way to make it dynamic as per the dictionary stored in the sheet in the image?

 

[Joe4]

Is there a way to make it dynamic as per the dictionary stored in the sheet in the image?

That is a whole different question that your original one, so that warrants a new thread.