Height + Width of a Range

[Stuart]

What is the quickest way to return the overall width
(ie sum of columnwidths) and height (ie sum of
rowheights) for a range, please?

Regards.

 

[Bob Flanagan]

Stuart, something like

rh=0
for each oRow in selection.rows
rh = rh + oRow.RowHeight
Next

I leave as an exercise the same code for column height!

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Don Guillett]

this will give the overall column width of cols 1-12. Use rows(i).rowheight
for rows
Sub sumcolwidths()
For i = 1 To 12 Step 1
mcw = mcw + Columns(i).ColumnWidth
Next
MsgBox mcw
End Sub
====for the range
Sub sumcolWrowH()
With Range("a1:d5")
mr = .Rows.Count
For i = 1 To mr Step 1
mrh = mrh + Rows(i).RowHeight
Next
MsgBox "total row height =" & mrh
mc = .Columns.Count
For i = 1 To mc Step 1
mcw = mcw + Columns(i).ColumnWidth
Next
MsgBox "total col width =" & mcw
End With
End Sub

 

[Stuart]

Many thanks to you both.

Regards.

Stuart, something like

rh=0
for each oRow in selection.rows
rh = rh + oRow.RowHeight
Next

I leave as an exercise the same code for column height!

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Don Guillett]

glad to help

 

[Stan Scott]

Stuart,

I'm using Excel 2002, but I don't think it makes a difference.

Range("myRange").Width
Range("myRange").Height

Return the information you need.

Stan Scott
New York City

 

[Don Guillett]

That does it for height better than what I sent but it is multiplying the
sum of the column widths by the height of the range. It is not giving the
sum of the column widths.

 

[Tom Ogilvy]

Width used with a range, returns the width of the range in points. It
doesn't

 

[Don Guillett]

I stand corrected. With the example I was using, it "seemed" to multiply.