Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
Hi Tendresse,
An alternative example with a bit more info to show how the individual ranges can be resized. Regards, OssieMac "OssieMac" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
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("D1:D4"), 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 "OssieMac" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting non adjacent cell ranges
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
highlight color missing when selecting cell ranges with CTRL key | Excel Discussion (Misc queries) | |||
Selecting adjacent cell | Excel Programming | |||
VIEWING adjacent/nonadjacent cells or cell ranges in Excel 2007 | Excel Discussion (Misc queries) | |||
selecting different cell ranges across sheets, to display on summary page | Excel Discussion (Misc queries) | |||
selecting adjacent cells | New Users to Excel |