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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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.