[SOLVED] VBA merging cells and set height

VBA merging cells and set height

In my spreadsheet, I try to horizontally merge cells in two columns. After merging them I want them to have the same width in sum as the one column before.

For i = 16 To 25
With ThisWorkbook.Worksheets(week_sheet_name)
    .Rows(i).AutoFit
    h = ThisWorkbook.Worksheets(week_sheet_name).Rows(i).RowHeight
    .Range("V" & i & ":W" & i).MergeCells = True
    .Columns(22).ColumnWidth = 40
    .Columns(23).ColumnWidth = 30
    .Rows(i).EntireRow.RowHeight = h
End With
Next

In this loop it autofits the rows to the perfect height (columnwidth of column 22 before the loop is 70), after that I save the height in the variable h and merge the cells V and W of row i. The columnwidth is set to 40 and 30 to be in sum the same as the one column before. Last step is to apply the height of the row as it was autofitted before. E.g. in one cell, the autofit makes it 144 in height, after the makro it is 264 which is the same amount as it would be if I would autofit it but with an cellwidth of just 40.

If I do it for just one row (without the for loop) it works perfectly. If I don't change the columnwidth of column 22 it works with the loop too, but I need the width to be 40.

How could I make it work?

Solution

If i move the.Columns(22).ColumnWidth = 40 and .Columns(23).ColumnWidth = 30 out of the loop, it works. My code now is

For i = 16 To 25
With ThisWorkbook.Worksheets(week_sheet_name)
    .Rows(i).AutoFit
    h = ThisWorkbook.Worksheets(week_sheet_name).Rows(i).RowHeight
    .Range("V" & i & ":W" & i).MergeCells = True
    .Rows(i).EntireRow.RowHeight = h
End With
Next
ThisWorkbook.Worksheets(week_sheet_name).Columns(22).ColumnWidth = 40
ThisWorkbook.Worksheets(week_sheet_name).Columns(23).ColumnWidth = 30