Whilst the single For-Each loop works personally I prefer to loop areas when
dealing with a potential multi area range, either with For-To or For-Each,
eg

Sub test()
Dim i&, j&, n&
Dim rng As Range, rArea As Range, cel As Range

Set rng = Range("A1:A3,A7:A10")
' the 5th cell in this range is A8, not A5

Debug.Print rng(5).Address ' A5 wrong
' this represents the 5th cell of the first area, which extends
' beyond the area

n = 0
For Each cel In rng
n = n + 1
If n = 5 Then Debug.Print cel.Address
Next

' or
n = 0
For Each rArea In rng
For Each cel In rArea
n = n + 1
If n = 5 Then Debug.Print cel.Address
Next
Next

'or
n = 0
For i = 1 To rng.Areas.Count
For j = 1 To rng.Areas(i).Count
n = n + 1
If n = 5 Then Debug.Print rng.Areas(i)(j).Address
Next
Next

End Sub

I can't find any reference to the "intermittent problem" you say Ron de
Bruin has referred to..

Regards,
Peter T


"OssieMac" wrote in message
...
I have just got back to my computer and done some more testing and the
only
way I can get it to work is the method that I posted before using For Each
loop. I have tested in both xl2002 and xl2007.

Just as an added extra, I have previously had problems in the interactive
mode actually selecting the visible cells in a filtered list for copy and
paste. All cells get selected and pasted. To get around it I have had to
Go
to Special and select visible Cells only. This is an intermittent problem
that Ron de Bruin MVP said "more people have experienced this problem".

Regards,

OssieMac