VBA Merge, wrap and adjust row height automatically

Peter Muller

Board Regular
Joined
Oct 15, 2018
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
if I understand correctly:
Try this on a copy of your file.

VBA Code:
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
 
Upvote 0
if I understand correctly:
Try this on a copy of your file.

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
if I understand correctly:
Try this on a copy of your file.

VBA Code:
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
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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