Macro to make rows with formulas the same row height as the rows the are linked to?

[tonywatsonhelp]

Hi everyone,
now heres an interesting one for you!

I have lots of cells in column E linked to another sheet called "Lal Assessment" with formulas, all the formulas i care about are either written like this
='Lal Assessment'!E11
or this
=IF('Lal Assessment'!E44="","",'Lal Assessment'!E44)

now what i would love is a Macro that column go down Column E range E10:E109 of active sheet,
and look aat the cell to see if it was a formula (but it must be a formula thats to do with 'Lal Assessment'! as there are some formulas for other sheets in this range
but basically if the cell is a formula and is for sheet 'Lal Assessment'! then that Row height =the row height of that one.

so in other words if you go down column E and Cell E15 is the formula ='Lal Assessment'!E11, then Row15 row height = 'Lal Assessment'!E11 row height?

can this be done?
please help if you can

Thanks

Tony

 

[Peter_SSs]

Give this macro a try.

Sub Row_Height()
  Dim RX As Object
  Dim c As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "('Lal Assessment'\!)([A-Z]{1,3}\d{1,7})"
  Application.ScreenUpdating = False
  For Each c In Range("E10:E109")
    If c.HasFormula Then
      If RX.Test(c.Formula) Then c.RowHeight = Sheets("Lal Assessment").Range(RX.Execute(c.Formula)(0).submatches(1)).RowHeight
    End If
  Next c
  Application.ScreenUpdating = True
End Sub