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

It only work for that particular merge cell!
"Dave Peterson" wrote in message
...
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

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

What are you trying to do?

crapit wrote:

It only work for that particular merge cell!
"Dave Peterson" wrote in message
...
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


--

Dave Peterson

Mayb I didnt explain clearly,
A17 is the address of the merge cell (a17:a18), B17 is (b17:b18) and so on
"Dave Peterson" wrote in message
...
I thought you were trying to find out if the merged area (A17:F17) had
something
in it?

What are you trying to do?

crapit wrote:

It only work for that particular merge cell!
"Dave Peterson" wrote in message
...
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


--

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.

crapit wrote:

Mayb I didnt explain clearly,
A17 is the address of the merge cell (a17:a18), B17 is (b17:b18) and so on
"Dave Peterson" wrote in message
...
I thought you were trying to find out if the merged area (A17:F17) had
something
in it?

What are you trying to do?

crapit wrote:

It only work for that particular merge cell!
"Dave Peterson" wrote in message
...
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


--

Dave Peterson


--

Dave Peterson