Offset Merged Cells

[JasonC-VBA]

How do you offset the address of merged cells in VBA?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Example:
StartingAddress = G14:G16
<o> </o>
And I want NewAddress = F14:F16

 

[Biz]

I'm sure there is better method. I can only think of

Sub Test()
 Range("G14:G16").Offset(0, -1).Resize(3, 1)
End Sub

Biz

 

[P.Holko]

I think the offset the address of first of the merged cells will be enough, try this:

Range("G14").Offset(0, -1).Resize(3, 1)

outcome will be the same as obtained with Biz's method

 

[Biz]

Thought of new way instead hard coding 3

Sub Test()
Dim myRange As Range
'   Set your range here
    Set myRange = Range("G14:G16")
  Range("G14:G16").Offset(0, -1).Resize(myRange.Cells.Count, 1)
End Sub

Alternatively using P.Holko

Sub Test()
Dim myRange As Range
'   Set your range here
    Set myRange = Range("G14:G16")
  nge("G14").Offset(0, -1).Resize(myRange.Cells.Count, 1)
End Sub

Biz

 

[Rick Rothstein]

Without knowing in advance that there are 3 cells merged together...

Dim MA As Range, NewAddress As String
.....
.....
Set MA = Range("G14").MergeArea
NewAddress = MA.Offset(, -1).Resize(MA.Rows.Count).Address

This will work if G14 is not part of a merged area as well (assuming that would be meaningful inside the overall program).

 

[Biz]

Without knowing in advance that there are 3 cells merged together...

Dim MA As Range, NewAddress As String
.....
.....
Set MA = Range("G14").MergeArea
NewAddress = MA.Offset(, -1).Resize(MA.Rows.Count).Address

This will work if G14 is not part of a merged area as well (assuming that would be meaningful inside the overall program).

Hi Rick,

Thanks for pointing out Range.MergeArea Property. I learnt something new thank you mate.

Biz

 

[JasonC-VBA]

That’s perfect. Thanks for the help!