Hi all,

I would like to define a range with variant in VBA program. For example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

One way:

For i = 1 to 20
Range("A" & i & ":F" & i).Select
Next i

another:

For i = 1 to 20
Range("A" & i).Resize(1, 6).Select
Next i

another:

For i = 1 to 20
Cells(i, 1).Resize(1, 6).Select
Next i



In article ,
"Yiu Choi Fan" wrote:

Hi all,

I would like to define a range with variant in VBA program. For example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

Set rng = Range("A" & i & ": " & "F" & i )

or

Set rng = Range("A" & i).Resize(1,6)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Yiu Choi Fan" wrote in message
...
Hi all,

I would like to define a range with variant in VBA program. For
example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't
know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

Sub test()
Dim i As Long, rng As Range

i = 2

Set rng = Range("A1:F1").Offset(i - 1, 0)
MsgBox rng.Address
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Yiu Choi Fan" wrote in message
...
Hi all,

I would like to define a range with variant in VBA program. For
example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't
know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

Thanks for all your suggestions!!


"Yiu Choi Fan" wrote in message
...
Hi all,

I would like to define a range with variant in VBA program. For
example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't
know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

try something like this (play with the syntax to get it right)

'your i will become the variable in your cell reference a1:f1 now is ai:fi
'so you need to have it start at 1
i = 1
'do your loop
for-loop
'use the & to concatonate within the range definition splicing in your
'variable i within your range declaration
range("a"&i&":f"&i&"")
'up your counter
i=i+1
'loop
loop

best of luck.

-seth


"Yiu Choi Fan" wrote in message ...
Hi all,

I would like to define a range with variant in VBA program. For example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

Just another one...

Sub Demo()
Dim R As Long
With Range("A:F")
For R = 1 To 10
.Rows(R).Select
Next R
End With
End Sub

Dana DeLouis

"Yiu Choi Fan" wrote in message
...
Hi all,

I would like to define a range with variant in VBA program. For
example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't
know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu