Simple VBA code to calculate ColumnWidth

[walkingaway]

Dear all,

I have just begun VBA coding and require some help.

In my spreadsheet, B2, C2, D2 are merged. The rest of the cells are not.

	A	B	C	D	E
1
2

I would like to write a simple VBA code that would calculate the total width of the merged cells. As of now, I am using:
MsgBox Columns("B").ColumnWidth + _
Columns("C").ColumnWidth + _
Columns("D").ColumnWidth + _

But it would be a problem should the number of merged cells increase. Is there any other VBA codes that could handle this calculation?

A huge thank you in advance.

[snb]

Avoid merged cells in VBA.

[walkingaway]

Dear snb,

Yes, I avoid merged cells as much as possible. However, I am working with a template from my supervisor and it is not convenient to make any changes.

[mancubus]

first, try convincing your boss to not use merged cells...

if you fail try below

change A1 to what you see in address bar when you select merged area.

Sub vbax_53185_merged_area_column_width()
    
    Dim cll As Range
    Dim MrgdColWidth As Double
    
    For Each cll In Range("A1").MergeArea
        MrgdColWidth = MrgdColWidth + cll.ColumnWidth
    Next
    
    MsgBox MrgdColWidth
End Sub

[walkingaway]

Wow mancubus, that worked perfect. Thank you so much. Could you briefly explain the code, I would very much like to learn how it is done.

Once again, thanks for your help.

[mancubus]

welcome.

it loops all the cells in merge area and adds their columwidths to a variable.


when you declare a numeric variable (such as MrgsColWidth As Double in the code i posted), its value is 0 before assigning any value.

in the first loop, MrgdColWidth = MrgdColWidth + cll.ColumnWidth >> MrgdColWidth = (0) + A1's ColumnWidth
in the second loop, MrgdColWidth = MrgdColWidth + cll.ColumnWidth >> MrgdColWidth = (0 + A1's ColumnWidth) + B1's ColumnWidth
...
...

[walkingaway]

Dear mancubus,

Thanks for your explanation on the loop, I understand it now (:

Have a great day mancubus, your help has been invaluable.

[mancubus]

you are welcome.

please mark the thread as Solved from Thread Tools for future references...