Selecting multiple rows

jonny · May 5th 2016

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

Code

Rows("8").EntireRow.Select
    Rows("11").EntireRow.Select
    Rows("14").EntireRow.Select
    Rows("20").EntireRow.Select

Thanks

skywriter · May 5th 2016

Re: Selecting multiple rows

Try this. :cool:

Code

Range("A8, A11,A14, A20").EntireRow.Select

jonny · May 5th 2016

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

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

Will much appreciate your help..

skywriter · May 5th 2016

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.

jonny · May 5th 2016

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.

Code

RowCollection = ""


Private Sub optTest_Click()
    RowCollection = RowCollection & "|" & "2" & "|" & "3" & "|" & "4" & "|" & "5" & "|" & "6" & "|" & "14" & "|" & "27" & "|" & "29" & "|" & "30" & "|" & "31" & "|" & "34" & "|" & "35" & "|" & "36" & "|" & "37"
End Sub


Private Sub optTH_Click()
    RowCollection = RowCollection & "|" & "25" & "|" & "29" & "|" & "34"
End Sub

Display More

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

skywriter · May 5th 2016

Re: Selecting multiple rows

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

Code

Sub RowsTest()
Dim RowCollection As String
RowCollection = ""
RowCollection = RowCollection & "A2" & "," & "A3" & "," & "A4" & "," & "A5" & "," & "A6" & "," & "A14" & "," & "A27" & "," & "A29" & "," & "A30" & "," & "A31" & "," & "A34" & "," & "A35" & "," & "A36" & "," & "A37" & ","
RowCollection = RowCollection & "A25" & "," & "A29" & "," & "A34" & ","
RowCollection = Left(RowCollection, Len(RowCollection) - 1)
Range(RowCollection).EntireRow.Select
End Sub

jonny · May 5th 2016

Re: Selecting multiple rows

Skywriter, you are BIG!!
Much appreciate your help, this works smoothly!

Thank you!

skywriter · May 6th 2016

Re: Selecting multiple rows

My pleasure, thanks for the feedback. :cool:

jonny · May 6th 2016

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

Private Sub optPlastic_Click()
    RowCollection = RowCollection & "A26" & ","
End Sub
Private Sub optSemi_Click()
    RowCollection = RowCollection & "A26" & "," & "A33" & ","
End Sub

skywriter · May 6th 2016

Re: Selecting multiple rows

You should post all the code so I can see what the copy code looks like and possibly fix it for you.

jonny · May 8th 2016

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.

Code

Global RowCollection As String


Private Sub optPlastic_Click() 
    RowCollection = RowCollection & "A26" & "," 
End Sub 


Private Sub optSemi_Click() 
    RowCollection = RowCollection & "A26" & "," & "A33" & "," 
End Sub 


    RowCollection = Left(RowCollection, Len(RowCollection) - 1)
 '   Union(Range("A1:A16"), Range(RowCollection)).EntireRow.Copy - is that the correct syntax to append the rows A1:A16 to the copied rows?
 
   Range(RowCollection).EntireRow.Copy
   
    Workbooks.Add
    ActiveSheet.Paste

Display More

skywriter · May 8th 2016

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

Private Sub optPlastic_Click()      
RowCollection = RowCollection & "A26" & "," 
End Sub 
 
Private Sub optSemi_Click() 
    RowCollection = RowCollection & "A26" & "," & "A33" & "," 
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:

Code

Global RowCollection As String
 
Private Sub optPlastic_Click()
    RowCollection = RowCollection & "A26" & ","
End Sub
 
Private Sub optSemi_Click()
    RowCollection = RowCollection & "A26" & "," & "A33" & ","
End Sub
 
 Sub Test()


RowCollection = Left(RowCollection, Len(RowCollection) - 1)
 '   Union(Range("A1:A16"), Range(RowCollection)).EntireRow.Copy - is that the correct syntax to append the rows A1:A16 to the copied rows?
 
Range(RowCollection).EntireRow.Copy
 
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
RowCollection = ""


 End Sub

Display More

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.

skywriter · May 8th 2016

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.

Code

Global RowCollection As String
 
Private Sub optPlastic_Click()
    RowCollection = RowCollection & "A26" & ","
End Sub
 
Private Sub optSemi_Click()
    RowCollection = RowCollection & "A26" & "," & "A33" & ","
End Sub
 
 Sub Test()
 Dim a, j


RowCollection = Left(RowCollection, Len(RowCollection) - 1)


a = Split(RowCollection, ",")
RowCollection = ""


For Each j In a
    If InStr(1, RowCollection, j & ",") = 0 Then RowCollection = RowCollection & j & ","
Next j


If Right(RowCollection, 1) = "," Then RowCollection = Left(RowCollection, Len(RowCollection) - 1)
 
Range(RowCollection).EntireRow.Copy
 
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
RowCollection = ""


 End Sub

Display More

jonny · May 8th 2016

Re: Selecting multiple rows

Working great!
If I want to copy constantly the rows A1:A16, what's the correct syntax to do that?

Code

Union(Range("A1:A16"), Range(RowCollection)).EntireRow.Copy

skywriter · May 8th 2016

Re: Selecting multiple rows

Try it and see what happens.

If rows 1 to 16 are already in your row collection you may have the same issue again, but if they are not then see if it works.

Selecting multiple rows

Participate now!

Share