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

Turms out that I can use Offset in some cases.

In my case each structure consists of the following, up tp 4 per row.

E.g.:

A1:B1 gets merged.
A2:B2 Not merged
A3:B3 Not merged
A4:B4 Not merged
A5:B5 Not merged

Due to the merge, I have to use Cells to refer to any of the cells A1:A5.
But, I can refeer to the cells B2:B5 using an Offset from the corresponding
A2:A5.

"Howard Kaikow" wrote in message news:%23

Due to the merge, I have to use Cells to refer to any of the cells A1:A5.
But, I can refeer to the cells B2:B5 using an Offset from the
corresponding
A2:A5.

That'll be because you are only merging cells in a single row, I assume.

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

I still have a cheque from Bell for the princely sum of ten cents $0.10
mailed to me from US to UK. A public phone had chewed up my last dime. I was
annoyed, without it even the operator could not connect me. Though she very
politely offered to refund. I couldn't believe it when the cheque arrived up
a long time later.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I still have a cheque from Bell for the princely sum of ten cents $0.10
mailed to me from US to UK. A public phone had chewed up my last dime. I
was
annoyed, without it even the operator could not connect me. Though she
very
politely offered to refund. I couldn't believe it when the cheque arrived
up
a long time later.

Waye back, when I was in graduate school ,I sold some stock to pay my living
expenses (in those daze, largely beer and pasta).

The broker made a mistake and shortchanged me $.25.
Next time I was in the area of the broker, I stopped in to get my $.25.

Instead of reaching into his pocket, and handing me $.25, and getting
re-imbursed from petty cash, I had to wait for them to print a check. Being
in grad school, I was silly enough to wait for the check.

I then went to a bar in which I knew the bartender.
I said "Pete can you cash a ckeck for me from Merrill Lybch?"

His eys popped open wide.
When I showed him the check, he loudly announced to the bar's patrons
"Howard is cashing a $.25 check from Merrill Lynch!".

Back in those daze a bottle of beer cost about $.40, so the $.25 didn't
cover it.

Actually, the bus fare to go downtown was even more, but I had to go
downtown anyway.
I wasn't about to let Merrill Lynch keep my quarter!

Yes, I could have called ML, but then look at the fun I would have missed!

Bye thee waye:

I have to process RowHeights separately from MergeCells, so I have two
approaches.

First, I determine the Areas for each row that will contain merge cells.
Each such row will have up to, currently, 4 such areas.
I then do the RowHeight adjustment,

I then have 2 choices on WHEN to do the MergeCells.

1. I can do it row by row just after I adjust rowheight. This has the
advantage of eliminating the area string length issue,
but requires extra merge operations.

2. Create an area string that has all the cells to be merged and do the
merge in one operation. This should be faster
than doing separate merges, but requires me to address the length of the
area string

"Howard Kaikow" wrote in message
...
Bye thee waye:

I have to process RowHeights separately from MergeCells, so I have two
approaches.

First, I determine the Areas for each row that will contain merge cells.
Each such row will have up to, currently, 4 such areas.
I then do the RowHeight adjustment,

I then have 2 choices on WHEN to do the MergeCells.

1. I can do it row by row just after I adjust rowheight. This has the
advantage of eliminating the area string length issue,
but requires extra merge operations.

2. Create an area string that has all the cells to be merged and do the
merge in one operation. This should be faster
than doing separate merges, but requires me to address the length of the
area string

My guess is you have several more choices. Don't know but might be worth
while doing all the calculation stuff first, incl row heights on a per row
basis. Store address or range objects to do merge's later. Might be worth
testing if faster to do the merges on a per column basis vs per row
(probably not).

I don't see keeping track of the string length a problem. In another thread
you said something about string conc as being slow but relative to anything
to do with cells it's probably trivial.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
"Howard Kaikow" wrote in message
...
My guess is you have several more choices. Don't know but might be worth
while doing all the calculation stuff first, incl row heights on a per row
basis. Store address or range objects to do merge's later. Might be worth
testing if faster to do the merges on a per column basis vs per row
(probably not).

I'm doing the RowHeight separately, as things get messed up if a Range has
areas from more than 1 row.
I build an array of the addressesm then use Join to construct the Range for
all the merges.

I don't see keeping track of the string length a problem. In another
thread
you said something about string conc as being slow but relative to
anything
to do with cells it's probably trivial.

Cannot use string length asd that is version dependent, and, worse yet,
incorrectly documented.
Here's a 2nd pass, not yet desk checked, even faster version. Note that
there is no string assignment
to slow down things.

sArea = Replace(Join(sAreas, ","), "$", "")
first = 1
lenDone = 0
iComma = 0
last = Len(sArea)
lenArea = last

On Error Resume Next
Do While lenDone < last
Do
' Debug.Print first, lenArea
' Debug.Print Mid$(sArea, first, lenArea)
Set rng = .Application.Range(Mid$(sArea, first, lenArea))
If Err.Number = 0 Then
lenDone = lenDone + lenArea + iComma
rng.MergeCells = vbTrue
If lenDone = last Then
Exit Do
Else
first = lenArea + 2
lenArea = last - first + 1
iComma = 1
End If
Else
i = InStrRev(Mid$(sArea, first, lenArea), ",")
lenArea = i - first
iComma = 1
Err.Clear
End If
Loop
Loop
On Error GoTo 0