Selecting multiple rows

  • How the below code could be replaced into one line where a row number is variable?

    Code
    1. Rows("8").EntireRow.Select
    2. Rows("11").EntireRow.Select
    3. Rows("14").EntireRow.Select
    4. Rows("20").EntireRow.Select


    Thanks

  • Re: Selecting multiple rows


    I'm actually have to run thru the RowCollection array, to append the rows into the range and then copy them into new workbook.
    Here is a kind of psevdocode:

    Code
    1. For Each MyStr In Split(RowCollection, "|")
    2. If MyStr <> "" Then
    3. RowsToCopy = rows(MyStr).EntireRow ==> New Workbook
    4. End If
    5. Next MyStr


    Will much appreciate your help..

  • Re: Selecting multiple rows


    What does the array look like and where is it coming from?


    Are you creating the array in code, ("5|7|9") or is it coming from a cell, how is it created?


    Post a sample of the array.


    I see you splitting on the "|" character, but I'd like to see what the array looks like.

  • Re: Selecting multiple rows


    1. I have a userform with the groups of OptionButtons
    2. Pressing each OptionButton appends predefined row numbers into global variable, called : RowCollection
    3. Once I press a button "Copy" the row numbers stored in RowCollection should be copies to the new workbook.



    As a delimiter I've set "|" , but it certainly could be changed (or entire concept could be changed).

  • Re: Selecting multiple rows


    Try this code to see how it selects the rows. :cool:


    Code
    1. Sub RowsTest()
    2. Dim RowCollection As String
    3. RowCollection = ""
    4. RowCollection = RowCollection & "A2" & "," & "A3" & "," & "A4" & "," & "A5" & "," & "A6" & "," & "A14" & "," & "A27" & "," & "A29" & "," & "A30" & "," & "A31" & "," & "A34" & "," & "A35" & "," & "A36" & "," & "A37" & ","
    5. RowCollection = RowCollection & "A25" & "," & "A29" & "," & "A34" & ","
    6. RowCollection = Left(RowCollection, Len(RowCollection) - 1)
    7. Range(RowCollection).EntireRow.Select
    8. End Sub
  • Re: Selecting multiple rows


    When I trying to copy a range , I'm getting an error : "That Command Cannot Be Used on Multiple Selections". How could be copied the unique rows only?

    Code
    1. Private Sub optPlastic_Click()
    2. RowCollection = RowCollection & "A26" & ","
    3. End Sub
    4. Private Sub optSemi_Click()
    5. RowCollection = RowCollection & "A26" & "," & "A33" & ","
    6. End Sub
  • Re: Selecting multiple rows


    The code is very simple, a user form with two option buttons. After both buttons are being pressed, two rows should be copied into a new workbook.


  • Re: Selecting multiple rows


    I did some testing and I only have issues when the row collection has the same row more than once.


    So if I run both of these:

    Code
    1. Private Sub optPlastic_Click()
    2. RowCollection = RowCollection & "A26" & ","
    3. End Sub
    4. Private Sub optSemi_Click()
    5. RowCollection = RowCollection & "A26" & "," & "A33" & ","
    6. End Sub


    Then the row collection ends up with row 26 in it twice and this seems to cause an error.


    So if you avoid making it so that row collection has the same row twice everything seems to work okay.


    I also recommend setting row collection back to an empty string after pasting it to the new sheet.


    So I came up with the following code:


    When I run both the first and second click code and the row collection ends up with 26 in it twice, I get an error.


    If I run just the second which includes row 26 and 33, but row 26 is only in row collection once, it works fine.

  • Re: Selecting multiple rows


    I should have thought of this before my post above.


    You should read my post above so you know what's going on with this post.


    I am splitting the row collection into an array and then making a unique string out of it.