How to define a Range with variant?

  • Thread starter Thread starter Yiu Choi Fan
  • Start date Start date
Y

Yiu Choi Fan

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
 
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)
 
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
 
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
 
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
 
Back
Top