Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help on Dynamic Range Selection
Hi All,
I need an urgent help with one of the macros I am working on Say I am at the cell A2. I need to select the range A2:G3. When I try to record macro for this, it reads Range("A2").Select Range("A2:G2").Select The Problem is that I dont need the Alphabetic reference as I need to repeat the step for every 3rd row (A5, A8, A11 etc )..I was trying to use the RC reference but its not working. Please help Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help on Dynamic Range Selection
Try this
Sub RangeSelect() Dim myRange As Range Dim aWS As Worksheet Set aWS = ActiveSheet Set myRange = aWS.Range("A2") Set myRange = myRange.Resize(2, 7) Do While myRange.Row < aWS.Rows.Count - 4 Set myRange = myRange.Offset(3, 0) Debug.Print myRange.Address Loop End Sub BTW, you don't need to say it's URGENT here. If people can answer, they will. This happened to be the first unanswered question I saw this morning. HTH, Barb Reinhardt "anshu" wrote: Hi All, I need an urgent help with one of the macros I am working on Say I am at the cell A2. I need to select the range A2:G3. When I try to record macro for this, it reads Range("A2").Select Range("A2:G2").Select The Problem is that I dont need the Alphabetic reference as I need to repeat the step for every 3rd row (A5, A8, A11 etc )..I was trying to use the RC reference but its not working. Please help Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help on Dynamic Range Selection
"anshu" skrev i en meddelelse
ups.com... Hi All, I need an urgent help with one of the macros I am working on Say I am at the cell A2. I need to select the range A2:G3. When I try to record macro for this, it reads Range("A2").Select Range("A2:G2").Select The Problem is that I dont need the Alphabetic reference as I need to repeat the step for every 3rd row (A5, A8, A11 etc )..I was trying to use the RC reference but its not working. Please help Thanks in advance Hi anshu Here's one way: Sub test() 'Leo Heuser, 11-7-2007 Dim CollectRange As Range Dim Counter As Long Set CollectRange = ActiveSheet.Range("A2:G2") For Counter = 3 To 12 Step 3 Set CollectRange = _ Union(CollectRange, CollectRange.Offset(Counter, 0)) Next Counter MsgBox CollectRange.Address End Sub -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help on Dynamic Range Selection
Right click sheet tabview codeinsert this. Now when you select a2, a2:g3
will be selected, etc. But WHY select when it is usually not desirable. Tell us more. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 And Target.Row Mod 3 = 2 Then Target.Resize(2, 7).Select End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "anshu" wrote in message ups.com... Hi All, I need an urgent help with one of the macros I am working on Say I am at the cell A2. I need to select the range A2:G3. When I try to record macro for this, it reads Range("A2").Select Range("A2:G2").Select The Problem is that I dont need the Alphabetic reference as I need to repeat the step for every 3rd row (A5, A8, A11 etc )..I was trying to use the RC reference but its not working. Please help Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help on Dynamic Range Selection
Thanks everyone for the replies...I could figure it out now...I
apologize for using the "Urgent" header...I was not aware of the protocols here as I am brand new...:-) Thanks once again for the codes. Though I could not understand any of the codes, I could somehow make Leo Heuser's code work. Thanks to all other too! Cheers, Anshuman On Jul 11, 9:08 pm, "Don Guillett" wrote: Right click sheet tabview codeinsert this. Now when you select a2, a2:g3 will be selected, etc. But WHY select when it is usually not desirable. Tell us more. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 And Target.Row Mod 3 = 2 Then Target.Resize(2, 7).Select End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "anshu" wrote in message ups.com... Hi All, I need an urgent help with one of the macros I am working on Say I am at the cell A2. I need to select the range A2:G3. When I try to record macro for this, it reads Range("A2").Select Range("A2:G2").Select The Problem is that I dont need the Alphabetic reference as I need to repeat the step for every 3rd row (A5, A8, A11 etc )..I was trying to use the RC reference but its not working. Please help Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help on Dynamic Range Selection
"anshu" skrev i en meddelelse
ups.com... Thanks everyone for the replies...I could figure it out now...I apologize for using the "Urgent" header...I was not aware of the protocols here as I am brand new...:-) Thanks once again for the codes. Though I could not understand any of the codes, I could somehow make Leo Heuser's code work. Thanks to all other too! Cheers, Anshuman You're welcome, Anshuman. Thanks for your feedback. Regards Leo Heuser |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying a selection of a selection of a range | Excel Worksheet Functions | |||
Dynamic Field Selection - Calculation | Excel Discussion (Misc queries) | |||
dynamic selection of cells | New Users to Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Macro - Dynamic cell selection | Excel Discussion (Misc queries) |