PDA

View Full Version : Solved: Help uderstanding this loop



blackrock
02-15-2013, 09:22 PM
Hi Could someone please help me understand this loop. It joins two or more ranges together as it loops. Thanks

Sub a()
Dim LR As Long, cell As Range, rng As Range, cells As Range
Dim newrange As Range
Dim counter As Variant

For Each cell In Sheet3.Range("ServiceDates")
If cell.Value <> "" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
rng.Select
End Sub
I think the first time it loops the first if statment is true.
For the rest of the times it loops first if statement is false else is true.
Is this correct and how does Set rng = Union(rng, cell) work. cheers

Trebor76
02-15-2013, 11:48 PM
Hi blackrock,

Welcome to the forum and to a fellow Aussie!!

The code goes through each cell in the "ServicesDates" named range and for those cells which have data in them it creates a range called rng.

For the first setting of the rng range you cannot use the Union function as it will error out as there's no other cell to create an union. Once there's been a cell assigned to the range then the Union function is used to keep appending the cells to the rng range. That's why the If statement has been used.

You should also check why there's been six variables declared yet only two get used :confused:

HTH

Robert