SOLVED

Excel change row height based on font size

Contributor

Hi,

 

Is there a way of making excel change the row height, dependant on the font size in the cells, e.g. if font size is 12, then make row height 18...

 

Thanks

 

Rob

17 Replies

@Robert1290 

Will all cells in a row have the same font size?

Are you willing to use VBA code?

@Hans Vogelaar

Yes they will, and there are merged cells too.

There will likely be 4 or 5 different font sizes in a document.

Happy for VBA too, you helped me on my other VBA code that I still use

Rob

@Robert1290 

Here is macro you can run:

Sub FixHeights()
    Dim rng As Range
    Application.ScreenUpdating = False
    For Each rng In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        rng.RowHeight = 1.5 * rng.Font.Size
    Next rng
    Application.ScreenUpdating = True
End Sub

 

@Hans Vogelaar

Thanks, I have ran this on my workbook, I am still getting the bottom of text being cut off in places. Is this because of the range used? Also, can you also write code that will increase a merged cell if not all of the wrapped text has fit into the cell intially?

Rob

@Robert1290 

Do you have horizontally merged cells (for example A1 and B1), or vertically merged cells (for example A1 and A2), or both?

@Hans Vogelaar

I have only horizontally merged cells. Thanks

Rob
@Hans Vogelaar

They are actually both horizontally and vertically.

Rob
best response confirmed by Robert1290 (Contributor)
Solution

@Robert1290 

That complicates things enormously. I'd either unmerge the cells, or resize the rows manually.

@Hans Vogelaar

Ok, so probably need to be manual then. Most of them are 1 size which means I can just apply to all rows. code can be written to just work on a row which has only been merged horzontally that would still help alot. Is this possible without it being too complicated?

Rob

@Robert1290 

Where are the merged cells? All in the same columns (for example B/C)? Or does it vary from row to row? And are there multiple merged cells in the same row?

@Hans Vogelaar

It can vary, but 95% of it are cells B - AJ are always merged.

30% of the time, 1 row, 20% 2 rows merged as well, 20% 3 rows merged 20% 4 rows, 10% more than 4 rows, all as well as horizontally B-AJ.

@Robert1290 

Again: I'd unmerge the cells.

@Hans Vogelaar

I have around 10,000 merged cells, so to unmerge, then re merge will take hours. I will have to take the first route you mentioned then and just complete the tasks manually.

@Robert1290 

Sorry about that. In the future, try to avoid merged cells, especially if you want to wrap text.

@Hans Vogelaar

In that instance, would you propose just making cell a1 a page width then and tall enough to accomodate wrap?

If so, within the same excel I am adding many tables, so I am using several columns.

@Robert1290 

That is probably not an attractive option.

@Hans Vogelaar

Not to worry, appreciate you trying to help. I will just stick with 1st option of manually doing it.