Cell/Row height auto fit dynamic text content from alternative worksheet

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I've spent some time searching the forums here and while there are many similar queries, nothing quite fits for me.

I have a two column multi row table. Column A is a list of names, and Column B is every job that person will do, therefore all the items in Column A are a single name, but each cell in column B can contain several text items. The data for column B 'jobs' is created with textjoin() from text in several rows/columns with CHAR(10) as an argument to create each entry on a new line but within the same cell. Most people have a single job, but some have more than one.

PersonJob
Person 1Job 1
Job 2
Job 3
Person 2Job 1
Person 3Job 4
Job 8
Person 4Job 6

As this data is dynamic and going to be changing frequently for a few weeks, what I am trying to achieve is that the row heights for every row auto fit height whenever the content updates. If it helps, there's a relatively limited range of cells that I amend to update the dynamic content (D52:P89 on a worksheet called jobs). If the cell heights don't auto fit height, I'm worried I might miss some jobs as they are hidden at the bottom of the cell.

I'm a total newby to VBA, and have spent several days amending this document while work is quiet. I've attached a couple of VBA modules so far (to do things like add last-saved date etc) but at the moment I am learning.

Any advice would be welcome.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
VBA Code:
dim range1 as range
Set range1 = Range("D52:P89")
With range1
    .WrapText = True
    .Rows.AutoFit
End With
 

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks for that jsb,

Sadly I am unable to get that code to work at the moment. I get a compile error 'Invalid Outside Procedure'. This is when I added the code you suggested as a new module via developer tools. Was this wrong?

I should also have clarified - the original data is on a different worksheet (source), and the sheet I wish to have to auto height work on is never directly edited (joblist).

Would I want your code on the source or jobslist sheet?

Many thanks for your suggestion
 

Watch MrExcel Video

Forum statistics

Threads
1,130,340
Messages
5,641,576
Members
417,223
Latest member
jelena_

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