Merge Cell Question

[crapit]

If there are 3 columns of merge cell i.e A1 (A1 & A2), B1 (B1 & B2), C1
( C1 & C2 )
can i refer it as range("a1:c1")

As for formula, can it be used ay a merge cell?

 

[Dave Peterson]

If you don't know the extend of the merge, you could just ask:

Option Explicit
Sub testme()

Dim myMergeArea As Range
Dim myRng As Range

With Worksheets.Add
'some test ranges
Set myMergeArea = .Range("a1:d3")
myMergeArea.Merge

Set myRng = .Range("a1")
MsgBox myRng.Address & vbLf & myRng.MergeArea.Address
'myrng.mergearea will be the merged area range

If myRng.MergeCells Then
MsgBox "it's merged"
Else
MsgBox "it's not merged"
End If

myRng.Formula = "=c99"

End With

End Sub

And I could assign a formula without a problem.

 

[crapit]

So the reference must be down inVisual Basic

If you don't know the extend of the merge, you could just ask:

Option Explicit
Sub testme()

Dim myMergeArea As Range
Dim myRng As Range

With Worksheets.Add
'some test ranges
Set myMergeArea = .Range("a1:d3")
myMergeArea.Merge

Set myRng = .Range("a1")
MsgBox myRng.Address & vbLf & myRng.MergeArea.Address
'myrng.mergearea will be the merged area range

If myRng.MergeCells Then
MsgBox "it's merged"
Else
MsgBox "it's not merged"
End If

myRng.Formula = "=c99"

End With

End Sub

And I could assign a formula without a problem.

 

[crapit]

I wanted to test whether the value in a merge cell is empty. As my worksheet
contain at least 7 merge cell in the same row, and it happen to be next to
each other.
Do I have to refer to
If Range("a17").Value = "" and Range("b17").Value = "" and
Range("d17").Value = "" and Range("e17").Value = "" and Range("f17").Value =
"" Then

End if

 

[Dave Peterson]

nope.

You can do it just by just checking the first cell in the mergearea.

Option Explicit
Sub testme02()
Dim myMergeArea As Range
With Worksheets.Add
.Range("a1:a10").Merge
.Range("a1").Value = "hi"
Set myMergeArea = .Range("a1").MergeArea
MsgBox IsEmpty(myMergeArea.Cells(1))
End With
End Sub

So if you don't even know the extent of the merged area, you can just look at
the first cell in the .mergearea.

 

[crapit]

Oops, I forgot to mention that the following cell are the address of the
merge cells
If Range("a17").Value = "" and Range("b17").Value = "" and

 

[Dave Peterson]

pick out any ole cell in that merged area:

And use Range("d17").mergearea.cells(1)

to find the first cell.

Oops, I forgot to mention that the following cell are the address of the
merge cells
If Range("a17").Value = "" and Range("b17").Value = "" and

 

[crapit]

It only work for that particular merge cell!

pick out any ole cell in that merged area:

And use Range("d17").mergearea.cells(1)

to find the first cell.

 

[Dave Peterson]

I thought you were trying to find out if the merged area (A17:F17) had something
in it?

What are you trying to do?

It only work for that particular merge cell!

 

[crapit]

Mayb I didnt explain clearly,
A17 is the address of the merge cell (a17:a18), B17 is (b17:b18) and so on

 

[Dave Peterson]

If these are just cells that contain values, maybe you can use:

if application.counta(range("a17:f17")) = 0 then
....

But CountA() will count cells with formulas that evaluate to "" as filled.

Mayb I didnt explain clearly,
A17 is the address of the merge cell (a17:a18), B17 is (b17:b18) and so on