Understanding This loop

[blackrock41]

Hi Could someone please help understand this loop. As i need this code for a joining a range of cells that have some empty cells.
Thanks great web site i have attached a workbook

Please Login or Register  to view this content.

'The first time through loop first cell checked and contains data. if statement = true
' The first time through loop rng is empty. if statment = true
' rng is set to first cell range.
'Second time through loop rng is still set to first cells rng and through out the rest of the loop iterations remains set to first cells range.
'The union method is joing the first cells range to the rest of the other cells ranges.
This this correct cheers

[:) Sixthsense :)]

Why not this?

Please Login or Register  to view this content.

[blackrock41]

Hi SixthSense what does the 23 do i the code you provided. Thanks

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

Microsoft Excel Help Extract

expression.SpecialCells(Type, Value)

expression A variable that represents a Range object.

Parameters

Name Required/Optional Data Type Description
Type Required XlCellType The cells to include.
Value Optional Variant If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type.

Return Value
Range

Remarks

XlCellType constants Value
xlCellTypeAllFormatConditions. Cells of any format -4172
xlCellTypeAllValidation. Cells having validation criteria -4174
xlCellTypeBlanks. Empty cells 4
xlCellTypeComments. Cells containing notes -4144
xlCellTypeConstants. Cells containing constants 2
xlCellTypeFormulas. Cells containing formulas -4123
xlCellTypeLastCell. The last cell in the used range 11
xlCellTypeSameFormatConditions. Cells having the same format -4173
xlCellTypeSameValidation. Cells having the same validation criteria -4175
xlCellTypeVisible. All visible cells 12

XlSpecialCellsValue constants Value
xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2