Cell references and merged cells

[Howard Kaikow]

Is there an explanation somewhere of how to handle column references
when there are merged cells in another row?

For example:

Private Sub TestOffset()
'Expect references to columns A, B, and C.
'Get references to columns AB(merged), C, and D, if there are merged
cells in other rows
Dim r As Long
Dim rng As Excel.Range

Range("A1").Activate
Set rng = Range("A1")
With rng
Debug.Print .Address
.Value = 1
.Offset(0, 1) = 2
.Offset(0, 2) = 3
For r = 0 To 12 Step 6
Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address,
..Offset(r, 4).Address, .Offset(r, 6).Address
Next r
End With

Range("A20").Activate
Set rng = Range("A20")
Range("A20:B20").Merge
With rng
Debug.Print .Address
.Value = 11
.Offset(0, 1) = 12
.Offset(0, 2) = 13
For r = 0 To 12 Step 6
Debug.Print .Offset(r, 0).Address, .Offset(r, 2).Address,
..Offset(r, 4).Address, .Offset(r, 6).Address
Next r
End With
End Sub

 

[Peter T]

For your purposes don't use Offset, eg

Sub test()
Dim rng As range
Set rng = range("A1")
rng.MergeArea.ClearFormats

With rng
Debug.Print .Offset(0, 2).Address ' C1
Debug.Print .Cells(1, 3).Address ' C1

.Resize(1, 2).Merge

Debug.Print .Offset(0, 2).Address ' D1
Debug.Print .Cells(1, 3).Address ' C1
End With
End Sub

If rng refers to more than one cell use With rng(1,1)

Regards,
Peter T

 

[Howard Kaikow]

For your purposes don't use Offset, eg

I'll give it a try but not today.
Went to bed at 7 AM today/last night.

Got up a few hours later to do errands, including stopping at bank to
deposit a whopping $5 check.

Bank gave me a recipt.
When I got home, I realized that I had not endorsed the check!

So, I'll just vege out rest of day.