Merge Cell Question

C

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?
 
D

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.
 
C

crapit

So the reference must be down inVisual Basic
Dave Peterson said:
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.
Click to expand...
 
C

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
 
D

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.
 
C

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
 
D

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
Click to expand...
 
C

crapit

It only work for that particular merge cell!
Dave Peterson said:
pick out any ole cell in that merged area:

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

to find the first cell.
Click to expand...
 
D

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!
Click to expand...
 
C

crapit

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

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
Click to expand...
 
Top