PDA

View Full Version : [SOLVED] Select entire row excep cells in merged area



Emily
07-03-2005, 07:50 AM
Suppose I have merged cells A1:C5
How to select entire row 3 except Range(A3:C3)?

Regards.
Emily

Bob Phillips
07-03-2005, 09:31 AM
Suppose I have merged cells A1:C5
How to select entire row 3 except Range(A3:C3)?

Bit weird, but it seems to work. Test like so

?notmerged(Range("3:3")).address



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

Emily
07-03-2005, 08:55 PM
xld (http://www.vbaexpress.com/forum/member.php?u=2139) :

Would you please elaborate on your function with code.

Thanks
Emily

Bob Phillips
07-04-2005, 12:50 AM
Would you please elaborate on your function with code.

I thought I had.



NotMerged(Range("3:3")).Select

Emily
07-04-2005, 06:21 AM
It works

Thanks