Automatically Change Row Height Based on Cell Contents?

[samcham]

Is there a way to change the row height based on the contents of a cell?

Here's what I've done:

1. I have a worksheet with about 6,000 or so lines, containing data for
over 100 departments.

2. I've created Subtotals, and used Ron DeBruin's code to create separate
worksheets for each department.

3. The subtotals came over to the individual worksheets just fine. Now I
want to change the row height of the rows that contain the subtotals to 25,
so the worksheets are easier to read. Each of the subtotal rows has the word
"Total" included in the entry in column B.

Is there a way to automate this task?

Thanks,

Sam.

 

[Office_Novice]

Try Columns("B") .AutoFit

 

[samcham]

How will that change the row height?

Try Columns("B") .AutoFit

 

[Office_Novice]

.AutoFit Will adjust the cells in the defined range to fit the contents of
the cells in that range

 

[JLGWhiz]

This should do what you want. Test it on a copy before permanent installation.

Sub colBrwHgt()
Dim lastRow As Long, wks As Worksheet
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set wks = ActiveSheet
For Each c In wks.Range("B2:B" & lastRow)
If c.Value Like "*Total*" Then
c.EntireRow.RowHeight = 25
End If
Next
End Sub

 

[samcham]

Thanks, but I'm not trying to autofit to the contents. I'm trying to
increase the row height for rows that contain the subtotals. The height of
those rows is already autofit to the contents, but I want it to be greater.
For example, the autofit height is 12, but I want it to be 25 if there's a
subtotal on that line.

Sam.

 

[samcham]

PERFECT! Thank you very much!

Sam.

This should do what you want. Test it on a copy before permanent installation.

Sub colBrwHgt()
Dim lastRow As Long, wks As Worksheet
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set wks = ActiveSheet
For Each c In wks.Range("B2:B" & lastRow)
If c.Value Like "*Total*" Then
c.EntireRow.RowHeight = 25
End If
Next
End Sub