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?

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 wrote:

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

So the reference must be down inVisual Basic
"Dave Peterson" wrote in message
...
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 wrote:

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

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" wrote in message
...
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 wrote:

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

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 wrote:

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" wrote in message
...
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 wrote:

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


--

Dave Peterson

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
Range("d17").Value = "" and Range("e17").Value = "" and
Range("f17").Value =
"" Then

"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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


--

Dave Peterson

pick out any ole cell in that merged area:

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

to find the first cell.

crapit wrote:

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
Range("d17").Value = "" and Range("e17").Value = "" and
Range("f17").Value =
"" Then

"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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


--

Dave Peterson


--

Dave Peterson