Select a number of cells - Rookie mistake

[GiraffetheGeek]

Hi all,

I must be brain dead today.

I am trying to get VB to select a number of cells based on the variable myline.

My code is:

Range("B" & myline, "E" & myline, "H" & myline, "K" & myline, "N" & myline).Select

I get the error message "Compile error: Wrong number of arguments or invalid property assignment"

I have drawn a blank. I know I've done this before but cannot remember how!

Also while I am here can anyone suggest a more efficient way of doing this if I need to increase the number of cells, ie increase from B,E,H,K,N on the same line to maybe 20 different cells on the same line?

Cheers in advance

 

[Norie]

This will work but it might be an idea to try some other way to select the cells, especially if you there are going to be 20 cells or more.

Range("B" & myline & ",E" & myline & ", H" & myline & ",K" & myline & ",N" & myline).Select

PS You probably don't need to select, depends on what you are doing next with the selection.

 

[shg]

One way:

    Dim iRow As Long
 
    iRow = 3
    Rows(iRow).Range("A1,E1,H1,K1,N1").Select

 

[Biz]

One way:

    Dim iRow As Long
 
    iRow = 3
    Rows(iRow).Range("A1,E1,H1,K1,N1").Select

Nice technique m8.

 

[GiraffetheGeek]

@ shg,

Can you explain how you code works? in my example the variable myline would change, depending on the line selected by the user.

So being a rookie I don't really follow how your code fits with this.

Appreciate the input tho'

 

[pgc01]

One way:

    Dim iRow As Long
 
    iRow = 3
    Rows(iRow).Range("A1,E1,H1,K1,N1").Select

Really nice one!

I usually use (or should I say, used until now ):

    iRow = 3
    Intersect(Rows(iRow), Range("A1,E1,H1,K1,N1").EntireColumn).Select

but yours is much better.

 

[shg]

Thank you, pgc, Giraffe.

Can you explain how you code works? in my example the variable myline would change, depending on the line selected by the user.

Any range has a range property (ad infinitum), and each range is taken relative to the range higher in the hierarchy.

In the example, the value of iRow is set in the code, but it could equally be taken based on the current selection:

Rows(Selection(1).EntireRow).Range("A1,E1,H1,K1,N1").Select

 

[shg]

Sorry, that last is bogus.

Selection(1).EntireRow.Range("A1,E1,H1,K1,N1").Select