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.

Try Columns("B") .AutoFit

"samcham" wrote:

> 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.

How will that change the row height?

"Office_Novice" wrote:

> Try Columns("B") .AutoFit
>
> "samcham" wrote:
>
> > 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.

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

"samcham" wrote:

> How will that change the row height?
>
> "Office_Novice" wrote:
>
> > Try Columns("B") .AutoFit
> >
> > "samcham" wrote:
> >
> > > 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.

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" wrote:

> 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.

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.

"Office_Novice" wrote:

> .AutoFit Will adjust the cells in the defined range to fit the contents of
> the cells in that range
>
> "samcham" wrote:
>
> > How will that change the row height?
> >
> > "Office_Novice" wrote:
> >
> > > Try Columns("B") .AutoFit
> > >
> > > "samcham" wrote:
> > >
> > > > 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.

PERFECT! Thank you very much!

Sam.

"JLGWhiz" wrote:

> 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" wrote:
>
> > 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.