UnMerge Cells inside a For... Next Loop

[Papa_Don]

Hi group,

I'm having some problems with a For/Next loop that has a "UnMerge" inside it. After the copy and paste is completed, the routine stops and doesn't complete the "Next" portion of the loop. Are they any thoughts as to why? What should I do to fix this?

Here is the code I'm using:

    For rowNumber = 2 To 10000        
        Cells(rowNumber, 4).Select
        cellDVal = Range("D" & rowNumber).Value
        If cellDVal = "" Then
            Exit Sub
        End If


        If ActiveCell.MergeCells = True Then
            rowsMerged = ActiveCell.MergeArea.Rows.Count
            Range("D" & rowNumber).UnMerge
            Range("D" & rowNumber).Copy Range("D" & rowNumber & ":D" & rowNumber + (rowsMerged - 1)) ' The routine is exiting here.
            rowNumber = rowNumber + (rowsMerged - 1)
        End If
    Next rowNumber

In advance, thanks for you explanations and ideas to fix. I look forward to hearing from you.

Don

 

[MARK858]

Try replacing everywhere you have ActiveCell with Cells(rowNumber, 4) although I am not 100% sure what you actually want to do with ActiveCell.MergeArea.Rows.Count.

 

[Papa_Don]

Mark, I'm using

[COLOR=#333333]ActiveCell.MergeArea.Rows.Count[/COLOR]

to count the number of rows that are in the range of the merged cells. This way I can pick up at the start of the next cell without having to iterate to the next row. In other words, if the Range is "D2:D5", it will continue the loop at D6.

FYI....

I've been able to get the loop to finish. I had to save the document, close it, then reopen it for it to run completely. I'm not sure why. But that seemed to do the trick.

I suppose I answered my own question.

Thanks for your help.

Don