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?



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

https://learn.microsoft.com/en-us/office/vba/api/overview/excel/object-model

but it is in the Word object model

https://learn.microsoft.com/en-us/office/vba/api/word.cell?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbawd10.chm2382)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%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

[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 ...... :fainted:

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:
30840