Hi, have vba code that I need some help... code below work what is intended to do, basically it looks for merged cell and auto fit/size those cell... my issue is the code run very, very slow... I'm looking to see if there is way to improve it...
Currently, code looking in column C and if finds merged cell with oversized text/content it will resized row high accordingly...
All my merged cells are in sequence starting at row C16 and down, also If helps, I have counter cell in F1 to tell me how many rows contains data.... so if F1 = 51, then I have 51 line of data from C16 down. So instead looking for each cell in column C maybe range from C16 + F1 down would be some sort of counter what to look for… how can I adapt code below…. or is there other alternative to slow performance of this code.
Regards
Currently, code looking in column C and if finds merged cell with oversized text/content it will resized row high accordingly...
All my merged cells are in sequence starting at row C16 and down, also If helps, I have counter cell in F1 to tell me how many rows contains data.... so if F1 = 51, then I have 51 line of data from C16 down. So instead looking for each cell in column C maybe range from C16 + F1 down would be some sort of counter what to look for… how can I adapt code below…. or is there other alternative to slow performance of this code.
Code:
' Auto fit merged cells
Sub FixMergedSequential()
Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer
Application.ScreenUpdating = False
For i = 10 To Range("C" & Rows.Count).End(xlUp).Row
On Error Resume Next
Set rng = Range(Range("C" & i).MergeArea.Address)
rng.MergeCells = False
cw = rng.Cells(1).ColumnWidth
mw = 0
For Each cM In rng
cM.WrapText = True
mw = cM.ColumnWidth + mw
Next
mw = mw + rng.Cells.Count * 0.66
rng.Cells(1).ColumnWidth = mw
rng.EntireRow.AutoFit
rwht = rng.RowHeight
rng.Cells(1).ColumnWidth = cw
rng.MergeCells = True
rng.RowHeight = rwht
Next i
Application.ScreenUpdating = True
End Sub
Regards