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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,715
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,349
Office Version
  1. 365
Platform
  1. Windows
Give this macro a try.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,751
Members
415,854
Latest member
Tutu123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top