Select 2 non-adjacent rows

[Ezguy4u]

Ok so I need a vba program that will select two non-adjacent rows and the row numbers need to be variables. Attached is an example of what I have so far. Range("2:2,4:4").Select won’t let me substitute variables for the 2 and 4. Rows(2).Select and Rows(4).Select I could substitute variables for the 2 and 4 but I don’t get 2 non-adjacent rows. Any help will be appreciated. Thanks in advance.

Sub Select1()
Dim N As Integer
Dim M As Integer

Range("2:2,4:4").Select

N = 2
M = 4

Rows(N).Select
Rows(M).Select

End Sub

 

[My Aswer Is This]

Why use variables?

 

[Joe4]

A bit of an odd request, but here is one way:

Sub MySelect()

    Dim N As Integer
    Dim M As Integer
    Dim r As String

    N = 2
    M = 4
    
    r = N & ":" & N & ", " & M & ":" & M
    
    Range(r).Select
    
End Sub

 

[Ezguy4u]

Ok so the example I am using here is simplified. My real example is like 30 rows and every month it gets bigger. As it gets bigger the pairings change. Now I have to admit I am focused on one solution. It's the way I do it manually. Maybe with a little more thought, there is another solution. Joe4 's program does fulfill my requirements. And for sure it is going into my library. Thanks

 

[Joe4]

If you have a bunch to do (like 30), I would store all the different row numbers in an array, and build the range and select it like this:

Sub SelectRows()

    Dim rws()
    Dim i As Long
    Dim rng As Range
       
'   Define rows to select
    rws = Array(2, 4, 7, 10)
   
'   Loop through array and build range
    For i = LBound(rws) To UBound(rws)
        If i = 0 Then
            Set rng = Rows(rws(i))
        Else
            Set rng = Union(rng, Rows(rws(i)))
        End If
    Next i

'   Select range
    rng.Select
   
End Sub

The only part of the code you should ever need to change is this part right here:

    rws = Array(2, 4, 7, 10)

where you can list all the rows you want in this range.

 

[Ezguy4u]

Joe4 and the hits just keep comin. Part of being with this Mr Excel community, is learning so many news ways to use VBA. Again this is definitely going into my Excel tool box. I am still working on how I can use these programs but being exposed to, for me, new ideas, is priceless. Thank You

 

[Rick Rothstein]

If you have a bunch to do (like 30), I would store all the different row numbers in an array, and build the range and select it like this:

Sub SelectRows()

    Dim rws()
    Dim i As Long
    Dim rng As Range
      
'   Define rows to select
    rws = Array(2, 4, 7, 10)
  
'   Loop through array and build range
    For i = LBound(rws) To UBound(rws)
        If i = 0 Then
            Set rng = Rows(rws(i))
        Else
            Set rng = Union(rng, Rows(rws(i)))
        End If
    Next i

'   Select range
    rng.Select
  
End Sub

The only part of the code you should ever need to change is this part right here:

    rws = Array(2, 4, 7, 10)

where you can list all the rows you want in this range.

Joe, you should probably change this line of code inside the loop...

If i = 0 Then

to this...

If i = LBound(rws) Then

just in case the user decides to use Option Base 1

 

[Peter_SSs]

Dim N As Integer
Dim M As Integer

VBA converts all integer values to type Long, even if they are declared as type Integer, so you might as well declare them as Long to start with. Besides, it is shorter to type anyway.

In relation to the original question with 2 (or a few) rows to select, using variables, you could also do it without the string manipulation this way

Sub my_Select()
  Dim N As Long, M As Long
  
  N = 2
  M = 4
  Union(Rows(N), Rows(M)).Select
End Sub

Or using the sort of 'Range' notation you were starting off with rather than 'Rows' like I used above

Sub my_Select_v2()
  Dim N As Long, M As Long
  
  N = 2
  M = 4
  Range(Replace(Replace("#:#,#:#", "#", N, 1, 2), "#", M, 1, 2)).Select
End Sub

If there are lots of rows, here is a slight variation to Joe's code that you could also consider, but it does make the assumption that the very last row in the worksheet will never be one of the rows to be selected.

Sub Select_Rows()
  Dim rng As Range
  Dim Rw As Variant
  
  Set rng = Rows(Rows.Count)
  For Each Rw In Split("3 5 7 8 10")  '<- These are the rows to select. Add more with spaces between.
    Set rng = Union(rng, Rows(Rw))
  Next Rw
  Intersect(rng, Rows("1:" & Rows.Count - 1)).Select
End Sub

The follow-up question is:
Given that it is extremely rare that you need to select a range to work with it, and selecting tends to slow your code considerably, why do you want to select these rows?
It may be that whatever you are going to do next can be done without the physical selection.

 

[Joe4]

Joe, you should probably change this line of code inside the loop...

If i = 0 Then

to this...

If i = LBound(rws) Then

just in case the user decides to use Option Base 1

You are not wrong, but that would have to be explicitly set by the user, correct (I believe it defaults to 0, right?).
I would doubt that users new to VBA would do that on their own without instruction from whoever is providing the code.

 

[Rick Rothstein]

You are not wrong, but that would have to be explicitly set by the user, correct (I believe it defaults to 0, right?).
I would doubt that users new to VBA would do that on their own without instruction from whoever is providing the code.

In that case, why not change this...

For i = LBound(rws) To UBound(rws)

to this...

For i = 0 To UBound(rws)

then?