Row Height

[JohnUK]

Hi All,
Can anyone help with this.
Is there a piece of code that will change the height of the Row/Rows if
there is data in columns.

For example:
If I had data from cells C6 through to C100, only those rows would be 15
instead of the default of 12.75. Because the data is forever changing, I
would want the rows to change with it.
Sounds rather pointless I know, but can it be done
Many thanks in advance
Regards
John

 

[Ryan H]

This macro will scan down column C and if the cell is totally empty it will
adjust the height to the default height 12.75, else row height will be 15.

Sub RowSizer()

Dim lngLastRow As Long
Dim rng As Range

lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row

For Each rng In Range("C1:C" & lngLastRow)
If IsEmpty(rng) Then
rng.RowHeight = 12.75
Else
rng.RowHeight = 15
End If
Next rng

End Sub


Hope this helps! If so, let me know, click "YES" below.

 

[Rick Rothstein]

The following procedure will make the row height resizing take place
automatically as you enter or remove data in the range C6:C100. Right click
the tab at the bottom of the worksheet you want to have this functionality,
select View Code from the popup menu that appears and then copy/paste the
following code into the code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6:C100")) Is Nothing Then
If Len(Target.Value) Then
Target.RowHeight = 15
Else
Target.RowHeight = 12.75
End If
End If
End Sub

Now, go back to your work sheet and enter something into any cell in the
range C6:C100 and watch the row height change.... now delete it and watch
the row height return to the default size of 12.75.

 

[Ryan H]

You could actually do this too without the loop.

Sub RowSizer()

Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row

' adjust all row height to 15
Cells.RowHeight = 15

' adjust blank cell rows in Col. C to 12.75
Range("C1:C" & lngLastRow).SpecialCells(xlCellTypeBlanks).RowHeight =
12.75

End Sub


Hope this helps! If so, let me know, click "YES" below.

 

[JohnUK]

Hey! You are both stars
Many thanks for your help