Need Help on Dynamic Range Selection

  • Thread starter Thread starter anshu
  • Start date Start date
A

anshu

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
 
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 said:
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
Click to expand...


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
 
Right click sheet tab>view code>insert 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
 
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
 
anshu said:
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
Click to expand...

You're welcome, Anshuman. Thanks for your feedback.

Regards
Leo Heuser
 
Back
Top