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.
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.