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

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

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.

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

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

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