Select row apart from a merged area

[Aussiebear]

A while ago, one of our highly thought of members wrote this section of code in reply to a request to select a row other than a merged area. The code works, ( mind you that was never ever doubted, as Bob hardly ever produced code that didn't work.)

Function NotMerged(testRange As Range) As Range
Dim rngMA As Range
Dim cell As Range
Dim rngExclude As Range
For Each cell In testRange
        Set rngMA = cell.MergeArea
        If rngMA.Address <> cell.Address Then
            If rngExclude Is Nothing Then
                Set rngExclude = cell
            Else
                Set rngExclude = Union(rngExclude, cell)
            End If
        End If
    Next cell
If rngExclude Is Nothing Then
        Set NotMerged = testRange
    Else
        Set rngMA = rngExclude.Cells(1, 1).MergeArea
        rngMA.MergeCells = False
        Set NotMerged = AntiUnion(testRange, rngExclude)
        rngMA.MergeCells = True
    End If
End Function


Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
Dim saveSet
    saveSet = SetRange
    SetRange.ClearContents
    UsedRange = 0
    Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
    SetRange = saveSet 
End Function

Experience tells us to stay away from "Merged cells" as they are a problem when using VBA. Can someone kindly break this down for me to follow? Particularly the lines

If rngExclude is nothing Then
Set rngExclude = cell

Isn't "cell" one of those meant to be not used words within Excel?

[Paul_Hossler]

Isn't "cell" one of those meant to be not used words within Excel?

[OPINION]

1. 'cell' in not in the Excel object model

https://learn.microsoft.com/en-us/of...l/object-model

but it is in the Word object model

https://learn.microsoft.com/en-us/of...6)%26rd%3Dtrue

so it can be used, although I find it confusing

However, my preferred style is to use more meaningful variable names to make the code self documenting -- even if it's more typing and makes it longer -- since when I come back to it after awhile, it helps jog the brain cells

2. Yes, Merged Cells can be confusing, so as a rule I usually try to avoid them, but as always rules are made to be broken

[/OPINION]

[georgiboy]

Experience tells us to stay away from "Merged cells" as they are a problem when using VBA. Can someone kindly break this down for me to follow? Particularly the lines

If rngExclude is nothing Then
Set rngExclude = cell

You cannot create a union from a single cell. The check is there to ensure that the rngExclude range is not empty. If it is empty, the code will set rngExclude to contain a single cell on the first pass. On subsequent passes, the code will see that rngExclude is not empty and will proceed to the else statement, where it will add the range to the UNION that includes the cell created on the first pass.

If you only left the line:

Set rngExclude = Union(rngExclude, cell)

On the first pass, the code will attempt to create a union with NOTHING and cell. However, NOTHING is not a range, so this will cause an error. The UNION function requires that all of its arguments be ranges, so this error will prevent the code from executing properly.

[Aussiebear]

Now I'm spinning out......

Give the Function a Name & Dim the variables

Function NotMerged(testRange As Range) As Range
Dim rngMA As Range
Dim cell As Range
Dim rngExclude As Range

Set up the Primary Loop to test

For Each cell In testRange
        Set rngMA = cell.MergeArea
        If rngMA.Address <> cell.Address Then
            If rngExclude Is Nothing Then
                Set rngExclude = cell
            Else
                Set rngExclude = Union(rngExclude, cell)
            End If
        End If
    Next cell

How does it know where to test, because at this point we actually haven't set set anything other than rngMA ( which I assume Bob means range Merged Area) = Cell.Address? Everything dimmed at this point is a range type variable. So this line

Set rngExclude = Union(rngExclude ,cell)

effectively means

. Set rngExclude= Union(cell, cell)

which joins the merged cells as a defined range, or am I reading this wrong?

The Secondary (Alternative) loop ......

If rngExclude Is Nothing Then
        Set NotMerged = testRange
    Else
        Set rngMA = rngExclude.Cells(1, 1).MergeArea
        rngMA.MergeCells = False
        Set NotMerged = AntiUnion(testRange, rngExclude)
        rngMA.MergeCells = True
    End If
End Function

We call the following section (Function) from within the line Set Not Merged = AntiUnion( testRange, rngExclude).

Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
Dim saveSet
    saveSet = SetRange
    SetRange.ClearContents
    UsedRange = 0
    Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
    SetRange = saveSet 
End Function

[georgiboy]

Yes rngExclude has been dimmed as a range but it has not yet been set so it is a range variable that = NOTHING

On the first run through the loop rngExclude = NOTHING as you have noted.

rngExclude is GOING to be a range that is built in increments so for that reason on the first pass of the loop it is nothing. That is why when rngExclude is nothing it just sets rngExclude to be the one cell:

Set rngExclude = cell

Once it has done that rngExclude will not be nothing for each subsiquent pass so it proceeds to build a range with UNION:

Set rngExclude = Union(rngExclude, cell)

This includes the first cell which wold now be: rngExclude, rngExclude becomes the union and 'cell' is what is added to the union with each further pass.

If you run the code as below adn step through it, you can see that on first pass rngExclude =nothing:
Untitled.png