J
Joergen Bondesen
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 ****
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 ****