Autofit row height with custom maximum

[MichaelSchulz]

I have a table that is populated by a data connection to an Access database. When there is new data available, the data can be refreshed by clicking the the Refresh button on the Data tab.


One column, called 'Description', holds varying length text strings. I have set the column width and formatted the column to have wrapped text.


The row heights are set to autofit. So a record with a short description could have a row height large enough to hold one or two lines of text while a record with a long description would have a larger row height to hold multiple lines of wrapped text.


I have been requested to adjust the format so that a maximum of only five lines is ever displayed on the screen or printed but that the full text remain in the cell so it can be read if desired.


So the question is: how to set the worksheet to auto-size the row height of the records without exceeding the equivalent of five lines of text, e.g. records with five or less lines of text are fitted but records with more have a maximum height set?

 

[mrmmickle1]

You can run a few tests depending on your font size but something like this will work for calibri 11 if wordwrap is already set = true ..... This should be added to the bottom of your refresh code. :

[COLOR=#0000ff]Sub [/COLOR]Test()

[COLOR=#0000ff]Dim [/COLOR]LRow [COLOR=#0000ff]As Long[/COLOR]

LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Rows("2:" & LRow).RowHeight = 75[COLOR=#008000] 'We start with Row 2 b/c I assume you have a header.[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

 

[MichaelSchulz]

You can run a few tests depending on your font size but something like this will work for calibri 11 if wordwrap is already set = true ..... This should be added to the bottom of your refresh code. :

[COLOR=#0000ff]Sub [/COLOR]Test()

[COLOR=#0000ff]Dim [/COLOR]LRow [COLOR=#0000ff]As Long[/COLOR]

LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Rows("2:" & LRow).RowHeight = 75[COLOR=#008000] 'We start with Row 2 b/c I assume you have a header.[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

What this does not do is autosize those records containing a description of less than five lines. This code makes everthing the same height.
I need something that:
If the Description contains less than five lines of wrapped text THEN the record is autosized down to the smallest height
else set the height to a maximum size that displays only five lines of text.

 

[mrmmickle1]

What is the Column Width of description? You'll need to adjust based on character length of the cell....