Stop Excel from Auto Resize Row Heights

[voodoo1226]

Hello all and Happy New Year!

I have a question which I've been searching the web for with no luck. I hope someone here can help. Thanks in advance!

I have a spreadsheet with about 2,000 rows. In one of the columns the text is wrapped. So rows can have different row height, depending if on the text in the cell is wrapped or not. Now whenever I change the font, or enter a text in a cell, or deleting text from a cell, etc., the entire spreadsheet auto change all the rows' height.

I understand that if I select the rows to set the height, Excel will not change the height automatically. The problem is Rows have different height intermittently, based on if a cell is wrapped with long texts. With 2,000 rows, it would take too long to go down the sheet to select individual row (or group of rows) to set the height separately.

So, is there a way to stop Excel to auto resize the rows no matter what? I've read someone said to unprotect the cells and protect the sheet, and that didn't work.

Again, any advice is greatly appreciated.

 

[Legacy 143009]

You best shot will be trying SelectionChange event. If the user clicks elsewhere the code may set that row to the old value.
For this, you'll need a setup like this:
1. Create a standard Module and paste this line in it:

Public heights As Variant, lastChangedCell As Long

2. Paste the code below inside WorkBook:

Private Sub Workbook_Open()
  Module1.lastChangedCell = ActiveCell.Row
  Dim lRow As Long, i As Long
  With Worksheets("Sheet1")
  lRow = .Cells(Rows.Count, "A").End(xlUp).Row
  ReDim Module1.heights(1 To lRow)
 
  For i = 1 To lRow
    Module1.heights(i) = Rows(i).Height
  Next
  End With
End Sub

3. Paste the code below in the WorkSheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  Module1.lastChangedCell = Target.Row
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Long
  With Application
  .EnableEvents = False
  Rows(Module1.lastChangedCell).RowHeight = Module1.heights(Module1.lastChangedCell)
  .EnableEvents = True
  End With
End Sub

I know it's a bit sketchy solution but will work

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Stop Excel from Auto Resize Row Height (Rows with different heights down the Worksheet)
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[voodoo1226]

Thank you. I will give it a try.

 

[voodoo1226]

You best shot will be trying SelectionChange event. If the user clicks elsewhere the code may set that row to the old value.
For this, you'll need a setup like this:
1. Create a standard Module and paste this line in it:

Public heights As Variant, lastChangedCell As Long

2. Paste the code below inside WorkBook:

Private Sub Workbook_Open()
  Module1.lastChangedCell = ActiveCell.Row
  Dim lRow As Long, i As Long
  With Worksheets("Sheet1")
  lRow = .Cells(Rows.Count, "A").End(xlUp).Row
  ReDim Module1.heights(1 To lRow)
 
  For i = 1 To lRow
    Module1.heights(i) = Rows(i).Height
  Next
  End With
End Sub

3. Paste the code below in the WorkSheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  Module1.lastChangedCell = Target.Row
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Long
  With Application
  .EnableEvents = False
  Rows(Module1.lastChangedCell).RowHeight = Module1.heights(Module1.lastChangedCell)
  .EnableEvents = True
  End With
End Sub

I know it's a bit sketchy solution but will work

Thank you, I will give it a try.

 

[voodoo1226]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Stop Excel from Auto Resize Row Height (Rows with different heights down the Worksheet)
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I am very sorry. I should have read the rules before posting.

Thank you.