Select a Range of (Variable) Rows

L

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
 
L

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top