VBA Merge, wrap and adjust row height automatically

[Peter Muller]

Good evening,

I have a Report that pulls info in from different sheets. Need the rows to merge, wrap and automatically adjust the row height when I run the macro/prior to printing it.

The rows to merge and wrap and auto adjust for height are B8:AE8, and this will be from row 8 to row 125 (approximately). The number of rows vary depending on the info being populated from my source sheets.
Sheet name "Daily" and it is password protected.

There is code available in this forum, but despite trying several options, I am still at a loss. Please help

 

[rpaulson]

if I understand correctly:
Try this on a copy of your file.

Sub Do_IT()

For r = 8 To Cells(Rows.Count, "B").End(xlUp).Row
For c = 3 To 31  ' Column C to AE
    If Cells(r, c) <> "" Then Cells(r, "B") = Cells(r, "B") & " " & Cells(r, c)
Next c

Range("C" & r & ":AE" & r).ClearContents
Cells(r, "B").WrapText = True
Rows(r).EntireRow.AutoFit
Next r

MsgBox "Finished"
End Sub

hth,
Ross

 

[Peter Muller]

if I understand correctly:
Try this on a copy of your file.

Sub Do_IT()

For r = 8 To Cells(Rows.Count, "B").End(xlUp).Row
For c = 3 To 31  ' Column C to AE
    If Cells(r, c) <> "" Then Cells(r, "B") = Cells(r, "B") & " " & Cells(r, c)
Next c

Range("C" & r & ":AE" & r).ClearContents
Cells(r, "B").WrapText = True
Rows(r).EntireRow.AutoFit
Next r

MsgBox "Finished"
End Sub

hth,
Ross

Thank you for helping me.

The row height is automatically adjusting, which is correct, but everything is wrapping in col "B" instead of first merging col "B:AE" before wrapping the text

 

[Peter Muller]

Thank you for helping me.

The row height is automatically adjusting, which is correct, but everything is wrapping in col "B" instead of first merging col "B:AE" before wrapping the text

I included this code before 'next c', so problem corrected, thank you very much.

Range("b" & r & ":AE" & r).Merge

IF no info in "B", can the merge function please execute?

 

[Peter Muller]

if I understand correctly:
Try this on a copy of your file.

Sub Do_IT()

For r = 8 To Cells(Rows.Count, "B").End(xlUp).Row
For c = 3 To 31  ' Column C to AE
    If Cells(r, c) <> "" Then Cells(r, "B") = Cells(r, "B") & " " & Cells(r, c)
Next c

Range("C" & r & ":AE" & r).ClearContents
Cells(r, "B").WrapText = True
Rows(r).EntireRow.AutoFit
Next r

MsgBox "Finished"
End Sub

hth,
Ross

Please ignore post #5, i was too hasty.

By inserting the code to merge "B:AE", the auto height no longer works.

I need:

Where there is information in Col B between rows 8 - 125, columns "B:AE" must merge then wrap, then auto fit. where there is no info in "B" then no merge

 

[rpaulson]

You cannot use the AutoFit feature for rows or columns that contain merged cells in Excel - Microsoft Support

Explains the fact that you cannot use the AutoFit feature to adjust rows or columns that contain merged cells. You must manually set the row height and column width by using the Format menu.

support.microsoft.com

 

[Peter Muller]

You cannot use the AutoFit feature for rows or columns that contain merged cells in Excel - Microsoft Support

Explains the fact that you cannot use the AutoFit feature to adjust rows or columns that contain merged cells. You must manually set the row height and column width by using the Format menu.

support.microsoft.com

Oh my, that's a pity. thank you so much for your assistance, the code that you gave will help with a different application