Selecting non adjacent cell ranges

[Guest]

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

 

[Gary Keramidas]

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

 

[Guest]

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("D14"), Range("F1:G4"))

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

Regards,

OssieMac

 

[Guest]

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

 

[Guest]

Hi Tendresse,

An alternative example with a bit more info to show how the individual
ranges can be resized.

Regards,

OssieMac

 

[Guest]

Sorry. didn't paste the new example. here it is


Sub MultipleRange2()
Dim myMultipleRange As Range
Dim eaRnge As Range
Dim newRnge As Range

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

With myMultipleRange
For Each eaRnge In .Areas
MsgBox eaRnge.Address
Set newRnge = eaRnge.Resize(eaRnge.Rows.Count - 1, _
eaRnge.Columns.Count)
MsgBox newRnge.Address
Next eaRnge
End With
End Sub


Regards,

OssieMac

 

[Guest]

Gary, don't worry .. i figured out what i did wrong. I forgot to name the
cells Feb and Mar. Must be time for me to go home.

Thank you very much for your help. Much appreciated.
Tendresse