How can I get the text to show in a row over 409 pts high?

Question

My task is to get text to print and display though it is longer than the 409 point row show. The sheet I'm working on is a Destination sheet from a Source sheet that can change often, but generally only 1 of 15 cells present this problem. Cell parameters are fixed so I can't change font or column width. On a spreadsheet I've made a Macro that will insert one or more rows, merge the required cells and change the row height to increase the cell enough but what " IF ... END IF" can I use to find rows larger than 408 points to trigger the Macro? Using Excel 2007. Thanks.

Answer 1

Try recording a Macro and changing the size of a row. Then inspect the VBA code that the macro generates to see how the size of the Row is generated in code.

You'll notice that the VBA code doesn't use pixels, so you'll have to do a conversion to find the equivalent of 409 pixels. After that, you can use a loop to find all of the rows that have a cell height greater than a certain value:

Dim lng As Long
lng = 1
Do While Not IsEmpty(Range("A" & lng).Value)
  If Rows(lng).RowHeight >= 306.75 Then
    'Insert the code to add a new row here.  When looking at the '
    'code in your macro, you can replace the row number (e.g. the "18:18" in  '
    'Rows("18:18") with the counter variable, lng, like so: Rows(lng).... '
    'If you want the *following* row, use Rows(lng+1) instead.
    ''
    'I'm not sure of the command to insert a new row, but if you do insert a '
    'new row, watch your counter.  You may need to add an additional  '
    'lng = lng + 1 into your code to account for the newly added row.'
  End If
  lng = lng + 1
Loop