Not sorted union range.
 

Hi NG

I'm union 2 SpecialCells type the best way I know.

In ('// Only test) I can see that my Union Range not is beginning with the
first cell, but with the firste cell from xlCellTypeFormulas and then
xlCellTypeConstants. Is there a way to "sort" NotEmptyCells.

snip start ****

'// My range is stepped Column by Columns.

On Error Resume Next

Dim NotEmptyCellsForm As Range
Set NotEmptyCellsForm = _
ColRange.Cells.SpecialCells(xlCellTypeFormulas, 23)

Dim NotEmptyCellsConst As Range
Set NotEmptyCellsConst = _
ColRange.Cells.SpecialCells(xlCellTypeConstants, 23)

On Error GoTo 0

If Not NotEmptyCellsForm Is Nothing And _
Not NotEmptyCellsConst Is Nothing Then

Dim NotEmptyCells As Range
Set NotEmptyCells = _
Application _
.Union(NotEmptyCellsForm, NotEmptyCellsConst)
ElseIf Not NotEmptyCellsConst Is Nothing Then
Set NotEmptyCells = NotEmptyCellsConst
ElseIf Not NotEmptyCellsForm Is Nothing Then
Set NotEmptyCells = NotEmptyCellsForm
End If

'// Only test
Dim NotEmptyCellsNew As Range
Set NotEmptyCellsNew = NotEmptyCells
Dim xx As Long
For xx = 1 To NotEmptyCells.Cells.Count
MsgBox NotEmptyCells.Cells(xx).Address
Next xx
snip end ****

--
Best regards
Joergen Bondesen