Need VBA Code for dynamic row height

[dsubash]

Hi,

I am looking for a code to have a dynamic row height in my report. I have a database with certain criterias. I also have multiple sheets with filtered data. The report runs from row 9 on each sheet.

I need the default row height to be 12 from row 9 to end of report. I also have fields which has wrap text in them.

So I am basically looking for a code that would allow me to have a default row height of 12 for all the rows and whereever wrap text is available in a row, then the row height should automatically autoadjust to fit the contents.

I have this code which is embedded into the main code, but it is not working properly. Any suggestions on how to correct this code?

Sub AdjustRowHeights(ws As Worksheet, lastRow As Long)
Dim r As Range
Dim cell As Range
Dim hasWrapText As Boolean

ws.Rows("9:" & lastRow).RowHeight = 12

For Each r In ws.Range("C9:E" & lastRow).Rows
hasWrapText = False

For Each cell In r.Cells
If cell.WrapText = True And cell.Value <> "" Then
hasWrapText = True
Exit For ' No need to check other cells in this row
End If
Next cell

If hasWrapText Then
r.EntireRow.AutoFit

End If
Next r
End Sub

This code works fine the autofit incase of availablility of wrap text, but the default row height of 12 is not maintained

Thanks in advance.
Subash D

 

[kvsrinivasamurthy]

Try changing this

If hasWrapText Then
r.EntireRow.AutoFit

End If

as

If hasWrapText Then
r.EntireColumn.AutoFit

End If