How to know width and height of merge cells?

[Bpk. Adi Wira Kusuma]

I merge 2 cells, A1+B1. I wanna know width and height of the merge cells.
How to know
it?

 

[Bob Phillips]

Dim cell As Range
If ActiveCell.MergeCells Then
For Each cell In ActiveCell.MergeArea
MsgBox cell.Address & " width = " & cell.ColumnWidth & " height
= " & cell.Height
Next cell
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bpk. Adi Wira Kusuma]

if I make width a cell equal as sum of each width
cell in merge cell, so is width a cell same width merge cell?

 

[Dave Peterson]

Or you could use the width of the merged area.

Option Explicit
Sub testme()

Dim myTotalWidth As Double
Dim myTotalHeight As Double
Dim myCell As Range
Dim myMergedCells As Range

With ActiveSheet
Set myMergedCells = .Range("a1:c3")
End With

myMergedCells.Merge
myTotalWidth = 0
For Each myCell In myMergedCells.Rows(1).Cells
myTotalWidth = myTotalWidth + myCell.Width
Next myCell

myTotalHeight = 0
For Each myCell In myMergedCells.Columns(1).Cells
myTotalHeight = myTotalHeight + myCell.Height
Next myCell

'are they equal?
MsgBox myTotalHeight & "=" & myMergedCells.Height & vbLf _
& myTotalWidth & "=" & myMergedCells.Width

End Sub

 

[Bpk. Adi Wira Kusuma]

Try to set columnwidth a cell with the value!!!
are they equal?

Hi, Dave............. Why does property "width" always be 144. Where does
the value come from?

 

[Dave Peterson]

Try widening some columns (and increasing rowheights) and try it again.

 

[Dave Peterson]

ps. .width is a readonly property. You may want to look at .columnwidth, too.

 

[Bpk. Adi Wira Kusuma]

May be, u dont understood that I want.
I merge 2 cells (A1+B1). eg: width of the cell is 20 ( width of A1 is 10,
and width of B1 is 10). So if I make/set width of E1 to be 20, why its look
as no equal. Because when I type in both cells with the same font, so number
character in the cell is not equal.

 

[Dave Peterson]

Maybe you want .columnwidth.

May be, u dont understood that I want.
I merge 2 cells (A1+B1). eg: width of the cell is 20 ( width of A1 is 10,
and width of B1 is 10). So if I make/set width of E1 to be 20, why its look
as no equal. Because when I type in both cells with the same font, so number
character in the cell is not equal.

 

[Bpk. Adi Wira Kusuma]

Yes, the main of my purpose is how to make height/width of a cell egual to
height/width of a merge cell. eg: width of merge cell is 20 ( width of A1 is
10, and width of B1 is 10). So I wanna make/set width of E1 to be equal to
the marge cell.

How to do it?

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim TotalColumnWidth As Double
Dim myCell As Range

With ActiveSheet
TotalColumnWidth = 0
For Each myCell In .Range("a1").MergeArea.Rows(1).Cells
TotalColumnWidth = TotalColumnWidth + myCell.ColumnWidth
Next myCell
.Range("E1").ColumnWidth = TotalColumnWidth
End With

End Sub

Yes, the main of my purpose is how to make height/width of a cell egual to
height/width of a merge cell. eg: width of merge cell is 20 ( width of A1 is
10, and width of B1 is 10). So I wanna make/set width of E1 to be equal to
the marge cell.

How to do it?

 

[Ragdyer]

Why does this sound like someone is trying to overcome *the* error message
of:

<<"Cannot paste because the copy area and the paste area are not the same
size and shape.">>
when using merged cells?

 

[Dave Peterson]

I didn't guess that. I figured that the OP was making some kind of form that
needed to be pretty (whatever that means).

Why does this sound like someone is trying to overcome *the* error message
of:

<<"Cannot paste because the copy area and the paste area are not the same
size and shape.">>
when using merged cells?