Automatically Auto-fit Row Height of a range of cells when any cell is updated (dynamic content)

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have some VBA code which I have in the worksheet code area and I think I am close to getting it to work but not sure what I am doing wrong.

The worksheet in question is never directly edited, but a range of cells D5:D30 pull their data from cells within other sheets (using ='sheet'!H1etc). By the nature of the content these cells are going to fill up quickly so I would like each row to auto-fit height based on the content on column D. Several people will be opening this document and won't know how to do this manually and as I am worried they may miss details that go beyond the size of the cell, I'd like this process to happen automatically whenever any of the content of column D is updated.

The code I have is

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an Action when they are changed.
    Set KeyCells = Range("D5:D30")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Change the height of the header row when one of the defined cdlls is changed
        Rows("1:30").EntireRow.AutoFit

    End If
End Sub

Any one able to point out what I might be doing wrong? Do I perhaps need to attached the VBA on to the worksheet I will be editing directly?

Thanks
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
Worksheet_Change event procedures only fire when data is manually updated directly on that sheet.
If the data changes on the sheet are the results of links or formulas, the Worksheet_Change event procedure will NOT fire.

Basically, you have two options:
1. If the updates are the results of formulas that pull data from another tab, if the data from that "other" tab is updated manually, then create a Worksheet_Change event procedure that fires when that tab is updated, and just have it update the other sheet.
2. Instead of using a Worksheet_Change event procedure, use a Worksheet_Calculate event procedure. This fires whenever a calculation is done on the sheet. However, note that you CANNOT identify which cell is being recalculated, so you cannot narrow it down. It will run whenever any cell anywehere on the entire sheet is recalculated. So, it could slow things down a bit, as this code may run a lot more than you actually want or need it to (cannot really limit it).

One other thing. Note that this reference is redundant and totally unnecessary:
VBA Code:
Range(Target.Address)
"Target" is already a range variable. So you can replace what you have above with just:
VBA Code:
Target
 

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
that's really helpful thank you - still learning a lot here.

So if I was to say the range of editable cells on the worksheet named 'list' is D92:EC92 effectively I want to add a worksheet change event which then triggers my rows D5:D30 in worksheet 'results'. I'm not even sure how to google doing this. Perhaps I've tried to do too much here.

Thanks for your suggestion
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
So, you would just want to put a Worksheet_Change event procedure on your "list" sheet that looks like this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("D92:EC92"), Target) Is Nothing Then
        Sheets("results").Rows("1:30").EntireRow.AutoFit
    End If
    
End Sub
 
Solution

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You are literally the best. That's been so helpful, and I really appreciate it
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,168
Members
417,129
Latest member
geekzilla

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