Question about a range

[Robert Crandal]

Suppose I want to select a specific range, such as:

Range("B10:Q10").Select

The above range exists on row #10, between
Column B and Column Q.

In my VBA program, the user is allowed to pick only
a row number, such as row 10. If I know the row
number, what are the possible ways to convert this
value into the above range?

The only thing that I can think of is the following code:

Range("B" & row_n & ":Q" & row_n).Select

In the above code, the variable "row_n" stores the
value of the row that a user can pick. I'm pretty sure
this will work. I just somehow think there are other
ways to achieve the same result, as opposed to
concatenating a bunch of strings to form a range string.

Are there other techniques here?

thanks

 

[Claus Busch]

Hi Robert,

Am Thu, 16 May 2013 01:47:13 -0700 schrieb Robert Crandal:

Range("B" & row_n & ":Q" & row_n).Select

try:
Intersect(Columns("B:Q"), Rows(ActiveCell.Row)).Select


Regards
Claus Busch

 

[Robert Crandal]

try:
Intersect(Columns("B:Q"), Rows(ActiveCell.Row)).Select

Wow, thanks Claus. That's a good one.

Just curious, but is it possible to specify that same range above
using other objects such as Cells(), etc?? It seems like Excel has
tons of ways to specify the same range.

Thanks again for your awesome help! 8)

 

[Claus Busch]

Hi Robert,

Am Thu, 16 May 2013 02:06:06 -0700 schrieb Robert Crandal:

Just curious, but is it possible to specify that same range above
using other objects such as Cells(), etc?? It seems like Excel has
tons of ways to specify the same range.

the arguments for Intersect are ranges. You can specify 2 ranges up to
30 ranges. You can use all objects that returns a range.


Regards
Claus Busch

 

[joeu2004]

If I know the row number, what are the possible ways to
convert this value into the above range?
The only thing that I can think of is the following code:
Range("B" & row_n & ":Q" & row_n).Select

try: Intersect(Columns("B:Q"), Rows(ActiveCell.Row)).Select

[....]
Just curious, but is it possible to specify that same range
above using other objects such as Cells(), etc?? It seems
like Excel has tons of ways to specify the same range.

I believe your original form is the most efficient. But to answer your
question, the following will work:

Range(Cells(row_n,"b"),Cells(row_n,"q"))

But AFAIK, each reference to a range involves some interprocess
(interthread) communication between Excel and VBA. So the
Range(Cells,Cells) form is probably slower than the
Range(string-concatenation) form.

For similar reasons, I would not use Intersect for this purpose. Not only
because of the potential for increased intercommunication with Excel, but
also because it is overkill. Intersect is intended for much more complex
situations. So I doubt that it is as efficient as even the
Range(Cells,Cells) form.

BTW, you should give serious thought to using the Select property. Recorded
macros (over)use it a lot. But usually, it is avoidable, and it is
significantly better to avoid it.

 

[witek]

Suppose I want to select a specific range, such as:

Range("B10:Q10").Select

The above range exists on row #10, between
Column B and Column Q.

In my VBA program, the user is allowed to pick only
a row number, such as row 10. If I know the row
number, what are the possible ways to convert this
value into the above range?

The only thing that I can think of is the following code:

Range("B" & row_n & ":Q" & row_n).Select

In the above code, the variable "row_n" stores the
value of the row that a user can pick. I'm pretty sure
this will work. I just somehow think there are other
ways to achieve the same result, as opposed to
concatenating a bunch of strings to form a range string.

Are there other techniques here?

thanks

Range (cells (row, 2), cells (row,17)).Select

check 17

 

[Robert Crandal]

BTW, you should give serious thought to using the Select property.
Recorded macros (over)use it a lot. But usually, it is avoidable, and it
is significantly better to avoid it.

Hi joeu2004,

I programmed a search feature into my VBA program. Each time a search
hit is found I use the "Select" operation simply to highlight a particular
row.
So, I'm using Select just to make a row easily visible to users.

Are you saying that I should NOT use this "Select" operation because it
causes interprocess communication between Excel and VBA? Is this
going to cause problems or bugs in my program? Just curious....

Robert

 

[witek]

Hi joeu2004,

I programmed a search feature into my VBA program. Each time a search
hit is found I use the "Select" operation simply to highlight a
particular row.
So, I'm using Select just to make a row easily visible to users.

Are you saying that I should NOT use this "Select" operation because it
causes interprocess communication between Excel and VBA? Is this
going to cause problems or bugs in my program? Just curious....

Robert

He is saying that everywhere you have construction

range(....).Select
Selection.something()

it can be replaced by

range(...).something()

selecting is not needed there and is used for nothing.
Macro recorder does that because you click cell so it thinks you want to
select it.

..Select can be used if you really want to show something to user.

Just be careful.
Run you code when worksheet is hidden or protected. You will be surprised.

 

[joeu2004]

I programmed a search feature into my VBA program. Each time a search
hit is found I use the "Select" operation simply to highlight a particular
row. So, I'm using Select just to make a row easily visible to users.

That is a valid use of Select. I do the same thing sometimes.

Are you saying that I should NOT use this "Select" operation because
it causes interprocess communication between Excel and VBA?

No. We cannot escape the interprocess communication. But it is good to
minimize it when it makes sense to do so. The operative words are "when it
makes sense".

Many people take a recorded macro and either modify it or think it serves as
a good programming model. This results in an over-use of Select and other
unnecessary features, which is grossly ineffiecient. Recorded macros are a
good way to see how to do something __in_general__. But usually, they can
and should be optimized.

For example, when entering 1, 2, 3 and 4 into A1, A2, A3 and A4, the
recorded macro is:

Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A3").Select
ActiveCell.FormulaR1C1 = "3"
Range("A4").Select
ActiveCell.FormulaR1C1 = "4"

Obviously, the following is better (and could be optimized further):

Range("A1") = 1
Range("A2") = 2
Range("A3") = 3
Range("A4") = 4

But that comment does not seem to apply to your use of Select.

Is this going to cause problems or bugs in my program?

No way to say without seeing the full macro. But generally, no. The
over-use of Select is just inefficient, usually not wrong.

 

[CellShocked]

Hi Robert,

Am Thu, 16 May 2013 02:06:06 -0700 schrieb Robert Crandal:


the arguments for Intersect are ranges. You can specify 2 ranges up to
30 ranges. You can use all objects that returns a range.


Regards
Claus Busch

Just give it a name.

highlight the cell range in question, and place the cursor in the cell
address box (upper left corner)and TYPE the range name you want to give
it. from then on, a reference to that range name is ONLY that specified
range. In the new excel (not sure on older versions) you can click on a
drop down in the cell address box and any named ranges will be listed.
Clicking on one will take you there, and highlight it.