Confused?

VBA isn't Monty Python's Flying Circus and you won't have to wait for
the next episode :)

but I agree that syntax is often an illusive friend...

(why do you think that VBA books generally run well over 1000 pages)
e.g. on string manipulation Ken Getz spends 90 pages (chapter1) in VBA
Developer's Handbook. Tedious work.. but get the basics right and the
rest will follow :)

Range(5:6) doesNOT work because in this context VBA interpreter does not
recognise 5:6 as a string but as an invalid number.

[5:6] DOES work and probably is the exception to the rule. [] is short
code to EVALUATE a named range,expects a string, resolves it to a range.

Read VBAhelp on those square brackets.. saves you a lot of typing when
working with ranges. [a1] = Range("A1")

A recent post illustrated a little quirk of this method...
Range("a1:e5").Cells(3,2)=Range("a1:e5")(3,2)=[a1:e5].cells(3,2).
but [a1:e5].(3,2) doesNOT work.


For your purpose with a varying row size of a known startaddress:
alternatively you cxuld use
Range("5:5").resize(2)
Rows(5).Resize(2)
[5:5].Resize(2)

Let me explain Tom's suggested method: Range(i & ":" & j)
the expression INside the brackets is evaluated first.

The & is a string concatenation operator. In THIS case the expression
resolves to a string (because of the ":")= this is acceptable as an
argument of the Range property of the worksheet object.
Note that Range(1 & 1) will fail, But Rows(1 & 1) will resolve to Row11

In your example where 1 is "fixed" could be coded as Range("1:" & i)
rather then your Range (1 & ":" & i)

cheerz..

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"jim c." wrote:

using macro recorder, select row 5, code returned =
Rows("5:5").Select

manually i can code
Rows(5).Select

Using macro recorder again, select rows 5&6, code returned=
Rows("5:6").Select

Why does Rows(5:6).Select not work?

The reason I would like to know is I had to use CStr() to
use a variable Row

Rows(1:i).Select
Rows("1:i").Select
Rows(1 & ":" & i).Select
All above do not work, I spent forever to come up with...

i = 10
a = CStr(1 & ":" & i)
Rows(a).Select

I read Tom Ogilvy's Post Reply to Christy Sep 19 2003
6:07PM and learned the following works...

Rows("1" & ":" & i).Select

Thank You Tom... I don't know where you learn this stuff
I like this way better...