Forum Discussion
Excel VBA to merge cell with different font style
Below is the excel data i have:
(Table 1) - each Project has mutiple rows and merged together
And i would like to make it like this:
(Table 2) - Each project has their own row
To unmerge those rows from merged rows to separate rows, I wrote two Macros
1. To unmerge the rows and copy the text from merged cell to the first corresponding cell (For example: Table 1 copy H8 & H9 to H7)
Sub ConcatenateRows_Generated() End Sub |
It will become like this:
Now you can see H8 & H9's text is copied to H7, but i need to delete Row 8 & 9 as its meaningless now.
2. remove all useless row
Sub DeleteEmptyRowsInRange_Updated() End Sub |
And now it become like this, each Project is using 1 row
However, the for that 3 columns for Date, they follow the first cell on each merged Project's row, so some of them become all strikethrough and some of them become underlined.
Anyone have any idea how can i combine those content without losing the format??
or can i reformat the text merged according to it's length for every cell i want it to combine?
Thank you very much~
8 Replies
- Unmerge cells like this?
https://b23.tv/TFuTwd8- something similar, but my case have underline and strikethrough so it is more complicated
f01 f02
2 <u>Unexpected low load</u>
P-23066667:<s> Availability</s>
3 Unexpected low traffic
P-23066632: Availability
4 other
Yes, that is true.
If play with html ,tags u and s are available to render underline and strikethrough.
select f01,f02 from unmerge_cells limit 5;
create temp table aa as
select fillna(f01) f01,f02 from unmerge_cells;
drop table if exists split_to_multiple;
create table split_to_multiple as
select f01,group_concat(f02,'<br>') f02 from aa group by f01;
select * from split_to_multiple;
To merge cells with different font styles while preserving the formatting in Excel VBA, you can use the following approach:
Sub MergeCellsAndPreserveFormatting() Dim ws As Worksheet Dim lastRow As Long Dim i As Long ' Set the worksheet to the appropriate one Set ws = ThisWorkbook.Sheets("Generated") ' Find the last row in Column A lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Loop through rows For i = lastRow To 3 Step -1 If ws.Cells(i, 1).MergeCells Then ' Check if the cell in Column A is part of a merged cell ' Copy the content of the merged cell ws.Cells(i, 8).Value = ws.Cells(i, 8).MergeArea(1, 1).Value ws.Cells(i, 9).Value = ws.Cells(i, 9).MergeArea(1, 1).Value ws.Cells(i, 10).Value = ws.Cells(i, 10).MergeArea(1, 1).Value ws.Cells(i, 11).Value = ws.Cells(i, 11).MergeArea(1, 1).Value ' Unmerge the cell ws.Cells(i, 1).MergeArea.UnMerge End If Next i End SubThe code serves as an example, changes or adjustments may be necessary.
This VBA code loops through the rows and checks if the cell in Column A is part of a merged cell. If it is, it copies the content of the merged cell to the respective columns (H, I, J, K) while preserving the formatting. Then, it unmerges the cell in Column A.
Make sure to adjust the column references and worksheet name to match your specific setup. Also, be cautious and back up your data before running macros, especially when modifying cell contents. The text and steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you
Was the answer useful? Mark them as helpful!
This will help all forum participants.
- Hi @NikeolinoDE, the code you wrote only unmerged the first column of my table.
The code serves as an example, changes or adjustments may be necessary.
