Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a variable range | Excel Worksheet Functions | |||
Use a Variable to select a range | Excel Discussion (Misc queries) | |||
Using variable to select rows | Excel Programming | |||
Select a Range Through a Variable | Excel Programming | |||
Select rows using a variable | Excel Programming |