Select a Range of (Variable) Rows
 

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


--
Loucas
------------------------------------------------------------------------
Loucas's Profile: http://www.excelforum.com/member.php...fo&userid=5509
View this thread: http://www.excelforum.com/showthread...hreadid=537359

Select a Range of (Variable) Rows
 

One way:

Dim X as long
dim Y as long

x = 5
y = 10

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

Loucas wrote:

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

--
Loucas
------------------------------------------------------------------------
Loucas's Profile: http://www.excelforum.com/member.php...fo&userid=5509
View this thread: http://www.excelforum.com/showthread...hreadid=537359

--

Dave Peterson

Select a Range of (Variable) Rows
 

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 Wrote:
One way:

Dim X as long
dim Y as long

x = 5
y = 10

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

Loucas wrote:

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

--
Loucas

------------------------------------------------------------------------
Loucas's Profile:
http://www.excelforum.com/member.php...fo&userid=5509
View this thread:
http://www.excelforum.com/showthread...hreadid=537359

--

Dave Peterson


--
Loucas
------------------------------------------------------------------------
Loucas's Profile: http://www.excelforum.com/member.php...fo&userid=5509
View this thread: http://www.excelforum.com/showthread...hreadid=537359

Select a Range of (Variable) Rows
 

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 mo

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

And even mo

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.

Loucas wrote:

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 Wrote:
One way:

Dim X as long
dim Y as long

x = 5
y = 10

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

Loucas wrote:

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

--
Loucas

------------------------------------------------------------------------
Loucas's Profile:
http://www.excelforum.com/member.php...fo&userid=5509
View this thread:
http://www.excelforum.com/showthread...hreadid=537359

--

Dave Peterson

--
Loucas
------------------------------------------------------------------------
Loucas's Profile: http://www.excelforum.com/member.php...fo&userid=5509
View this thread: http://www.excelforum.com/showthread...hreadid=537359

--

Dave Peterson