Select a Range of (Variable) Rows

[Loucas]

Hi,

I suspect this might not be too difficult, but I'm a VBA novice! "Help"
didn't help, so here I am...

I am trying to select a Range of Rows by using Variables to define the
starting and ending Rows.

The command *Rows("X:Y").Select* would have worked if I had fixed rows
to select. I.e. if I knew that my range should contain rows 3 to 5 then
*Rows("3:5").Select* would have been ok.

The problem is that I want X and Y to be variables, established in
between the programme flow. That is, I want the programme to identify a
number of consecutive Rows, and select them all together so as to
perform some action on them.

I face this problem often, when I can find acommands in VBA that
correspond to strings of fixed values for a range, and not to
variables.

Any help?

Many thanks

 

[Dave Peterson]

One way:

Dim X as long
dim Y as long

x = 5
y = 10

rows(x & ":" & y).select

 

[Loucas]

Thanks Dave,

In the meantime I also tried

With Worksheets(1)
..Range(.Rows(3), Rows(5)).Select
End With

and it worked!

Your's suits me best.

Thanks again

 

[Dave Peterson]

There are lots of ways to do this kind of thing.

But if you use your code, watch out for those leading dots!

With Worksheets(1)
.Range(.Rows(3), .Rows(5)).Select
End With

Sometimes the leading dot gets eaten in the newsgroup, but the .rows(5) still
needed that dot.

If you don't qualify the range, then excel will use the active sheet--which may
not be worksheets(1) (well, if the code is in a general module).

And one more:

with worksheets(1)
.range(.cells(x,1),.cells(y,1)).entirerow.select
end with

And even more:

with worksheets(1)
.cells(x,1).resize(y-x+1).entirerow.select
end with

No matter which one you chose (and I bet it'll come down to personal
preference), you'll want to be able to read other people's code.