Home>

There is a column that merges cells from columns C to K one row at a time. Since comments are posted to each merged cell in VBA, some cells may be out of content.
I want to do the work of the subject in each merged cell with VBA, but I get an error saying "Unable to set Columnwidth property of Range class" in the code of line 16.
By the way, the original procedure picked up from the net does not process each merged cell with the for next syntax, and sets the range of the code in line 7 directly.Therefore, it works, but it is the processing for only one row of merged cells.

I would appreciate it if anyone could teach me.

1: Sub test ()

2: Dim echRng As Range, allRng As Range
3: Dim colWdtSum As Double
4: Dim colWdtLft As Double
5: Dim n As Integer

6: For n = 11 To 97
7: Set allRng = Range (Cells (n, 3), Cells (n, 11))
8: ReDim colWdtArr (allRng.Column To allRng (allRng.Count) .Column)
9: If allRng.Rows.Count>1 Then
10: Exit Sub
11: colWdtLft = allRng (1) .ColumnWidth

12: For Each echRng In allRng
13: colWdtSum = colWdtSum + echRng.ColumnWidth
14: Next echRng

15: allRng.UnMerge
16: allRng (1) .EntireColumn.ColumnWidth = colWdtSum
17: allRng (1) .WrapText = True
18: allRng (1) .EntireRow.AutoFit
19: allRng.Merge
20: allRng (1) .ColumnWidth = colWdtLft

21: Next

22: End Sub

  • Answer # 1

    Please insert the following line between the 6th and 12th lines.

    colWdtSum = 0


    This value grows infinitely because it is not initialized during the loop, and an error occurs around 300.

    However, after the process with the error, the same ColumnWidth is overwritten in the 20th row, and the columns being operated are the same all the time, so in the loop, only the maximum value of colWdtSum or colWdtLft is determined, and the loop I feel that it is better to set it once after exiting.

Related articles