Clever Geek Handbook

How do I get the full height and width of a range including borders?

I can't seem to find anything.

In VBA, given Dim r As Range: Set r = Range("C20:C21")

I can get r.height

and r.width

where the first is the combined height of cells C20 and C21, but that doesn't include the size of the borders between them.

Is there a reliable method to get the full combined height and width (ex: height and width of cells + size of the borders between those cells)?

Thank.

+3


source to share


2 answers


I think your calculation should be

testCB.Top = testCB.Top + ((r.Height - testCB.Height) / 2))


Or rather (not sure what your version means)

testCB.Top = r.Top + ((r.Height - testCB.Height) / 2))
0


source


This can get the weight of the border.

'If all borders are equal, you can take the weight as follow:
Range("C20:C21").Borders.Value

'If you want a specific border, you can use as follow:
Range("C20:C21").Borders(xlEdgeLeft).Weight

If this works for you, your calculations will be as follows:



height = Range("C20:C21").Height + _
         Range("C20:C21").Borders(xlEdgeBottom).Weight + _
         Range("C20:C21").Borders(xlEdgeTop).Weight

width = Range("C20:C21").Width + _
        Range("C20:C21").Borders(xlEdgeLeft).Weight + _
        Range("C20:C21").Borders(xlEdgeRight).Weight

And if you also need a border between two cells for height, you can use the following:

height = Range("C20:C21").Height + _
         Range("C20:C21").Borders(xlEdgeBottom).Weight + _
         Range("C20:C21").Borders(xlEdgeTop).Weight + _
         Range("C20:C21").Borders(xlInsideVertical).Weight
0


source






More articles:


All Articles