Hi Gary, Thanks a lot for your reply.
Your code partially worked. It selected the first range, deselected the last
row and cleared the contents of that range. Then i got the error message:

Run Time Error '1004':
Method 'Range' of object '_Global' failed.

I'm not sure what that mean!
I tried the exact code but added (Next i) instead of just (Next) but i
still get the same error. Any clues?
Thanks again, Tendresse

"Gary Keramidas" wrote:

just throwing this out as an idea:

Sub test()
Dim i As Long
Dim arr As Variant
arr = Array("E10:Jan", "J10:Feb", "P10:Mar")

For i = LBound(arr) To UBound(arr)
Range(arr(i)).Resize(Range(arr(i)).Rows.Count - 1).ClearContents
Next
End Sub

--


Gary


"Tendresse" wrote in message
...
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