Defining a Range using Variables

  • Thread starter Thread starter Billyruben
  • Start date Start date
B

Billyruben

This is VBA 101 for me so your help is very much appreciated.

The problem lies in the syntax between the parenthesis in defining a Range.

OK, so an area between A3 and D30 is coded as Range("A3:D30").Select

Assume that the range is defined by one cell only: If A3 is located by
trapping the last row in column A by using a Variable, the code is Range("A"
& VarX).Select (No problem there.)

Now, assume that the range is defined by A3 and any row in column D: if D30
is located by trapping the last row in column D by using a Variable, the
area is defined as Range("A3:D" & VarY).Select (No problem there.)

The Big Question is: How do to code an area defined by using two Variables?
Given the area is defined by A(VarY) down to D(VarX), the code Range("A"&
Var ":D" & VarY).Select does not work. What is the correct syntax in this
case?

(Assume that both variables have been properly "Dimm"ed and executed.)
 
Check your typing. Your syntax is O.K.

Sub billy()
VarY = 2
VarX = 7
Range("A" & VarY & ":D" & VarX).Select
End Sub
 
Hi,

Your basic idea is fine you just lost an &:

Range("A" & Var & ":D" & VarY)

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Another one:

With activesheet
.range(.cells(vary,"A"),.cells(varx,"D")).select
end with
 
Back
Top