PDA

View Full Version : [SOLVED] Simple VBA code to calculate ColumnWidth



walkingaway
07-15-2015, 12:03 AM
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
07-15-2015, 12:05 AM
Avoid merged cells in VBA.

walkingaway
07-15-2015, 12:08 AM
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
07-15-2015, 12:35 AM
first, try convincing your boss to not use merged cells...

if you fail :D 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
07-15-2015, 12:45 AM
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
07-15-2015, 01:20 AM
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
07-15-2015, 02:02 AM
Dear mancubus,

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

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

mancubus
07-15-2015, 04:02 AM
you are welcome.

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