Mar 08 2023 08:05 AM
I am creating a spreadsheet and want to set my rows to adjust according to how many rows of text contained within the cells BUT not to be less than a specific height (43)... is there a way to do this?
Mar 13 2023 09:57 AM
This solution is written for the Windows version of Excel, but I expect it would work for the Mac version also. You can use the following procedure to accomplish that:
Sub SetRowHeights()
' For the active worksheet, this procedure changes all rows to the heights
' that Excel deems necessary to show all data, but then enforces a
' minimum row height.
Dim strActiveCellAddress As String
Dim in4LastRowOfData As Long
Dim in4Row As Long
Dim objRow As Range
'---- Capture the location of the active cell.
strActiveCellAddress = ActiveCell.Address
'---- Adjust the row height for all rows per Excel's autofit rules.
Cells.Select
Cells.EntireRow.AutoFit
'---- For each row (from the top down), enforce a minimum height.
With ActiveSheet
in4LastRowOfData = .UsedRange.Rows(.UsedRange.Rows.Count).Row
End With
' --
For in4Row = 1 To in4LastRowOfData
Set objRow = Range(CStr(in4Row) & ":" & CStr(in4Row))
If objRow.RowHeight < 43 Then
objRow.RowHeight = 43
End If
Next in4Row
'---- Change the selection to just the originally active cell.
Range(strActiveCellAddress).Select
End Sub
You have the option of saving this procedure in a particular workbook or in the special workbook Personal.xlsb. (See Create and save all your macros in a single workbook for the latter choice.)
I recommend invoking this procedure as a macro, when desired.
You could add an event handler (Workbook_SheetChange or Worksheet_Change) in any desired workbook or worksheet that would invoke this procedure automatically. But you likely will not want to have all that additional processing occur when any cell content changes.