Hi Tendresse,

See if this little example gives you the ideas that you need. I'm sure that
you will be able to manipulate the code for your requirements.

Sub MultipleRange()
Dim myMultipleRange As Range

Set myMultipleRange = Union(Range("A1:B4"), _
Range("D1:D4"), Range("F1:G4"))

With myMultipleRange
For i = 1 To .Areas.Count
MsgBox .Areas(i).Address
Next i
End With
End Sub

Regards,

OssieMac

"Tendresse" wrote:

Hi all,
I worte a code to select a range of cells, deselect the last row in the
range then clear the selection's contents. The code is:

Range("E10:Jan").Select
Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select
Selection.ClearContents

The problem is that i have 20 non-adjacent ranges that i want to apply the
above code to. Is there a way to loop through non-adjacent selections rather
than having to type this code 20 times?

I managed to select the non-adjacent ranges using one line as follows:
Range("E10:Jan, J10:Feb, P10:Mar, ..... etc").select

but i don't know how to resize all of these ranges together. I tried using
the offset method hoping to shift all the selections one row up, but it
worked on one selection only rather than the whole lot.

Is there something similar to
For each Selection in activesheet.selections
Deselect the last row in the selection
Selection.ClearContents
Next Selection

Any ideas? i'm using Excel 2003
Many thanks
Tendresse