Select Loop in VBA only selects last row

Question

I have a worksheet (#1) containing a list of row numbers that correspond to worksheet #2. There are about 650 rows that I need to select in worksheet #2 (out of a total of 11,000ish rows in worksheet #2).

I got as far as making this little vba script

Sub SelectRows()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set wb1 = Workbooks("worksheet1")
Set wb2 = Workbooks("woorksheet2")

Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet2") 

For Each Row In ws1.Range("A1:A650").Cells
    ws2.Rows(Row).Select
Next
End Sub

The problem I run into, is that after the script runs, it will only have selected the very last row# from worksheet#1.

so if worksheet#1 looks like this...

1

6

100

...

5670

I am only able to select row 5670 on worksheet#2 after running my program.

How can I tell VBA that I want to continually add to my selection instead of just re-selecting the next row?

EDIT For those who are unclear..

I have a large spreadsheet containing over 11,000 rows of data. I need to select around 600 rows from this spreadsheet. However, I only know the row# of the data that I need to select from this spreadsheet.

So, I have created a second spreadsheet that lists in A1:A600 the row numbers that I need to select in my aforementioned spreadsheet.

Yes, this is a terrible way to do things. Yes, if there was any other way of selecting data other then row#, I would do it. Simply put, in my situation, there is not. If you do not believe I have been trying to solve this problem all day, that is fine, you may downvote this question.

What would you like to do with this selection? [There are usually better ways than using select to accomplish most tasks](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros).. — Ioannis, Aug 17 '15 at 18:12
@findwindow could you elaborate? I am using a range() in my for each loop... can you tell me where / how I can implement a range() to solve this problem? — Christopher, Aug 17 '15 at 18:13
@loannis I am just trying to select these so I can collect my data without making changes to my worksheets. I will eventually use copy + paste after I have my selection made. — Christopher, Aug 17 '15 at 18:14
You're using rows() now. That's why it only selects one row. Look up range(). But I second ioannis, what are you trying to do? — findwindow, Aug 17 '15 at 18:14
If you're just copy/pasting, just copy/paste the range. Why loop it? — findwindow, Aug 17 '15 at 18:15
I don't understand how copy/paste a range of row numbers would get me any further in selecting that range of row numbers in my second workbook. — Christopher, Aug 17 '15 at 18:23
Uh, you're not making much sense. You said above "eventually use copy + paste after I have my selection made" but it seems you want to do something else? We can't read your mind so you've to articulate clearly or we can't help you :/ — findwindow, Aug 17 '15 at 18:37
OH. Your edit makes sense. Uh, why don't you select/copy/paste/move one row at a time? — findwindow, Aug 17 '15 at 18:45
I don't have much experience in VBA, and I thought that just making the selection, then going to the sheet and doing ctl+c, then pasting to a new spreadsheet would be easiest. — Christopher, Aug 17 '15 at 18:52
Yea, when you're new, just explain in English what you want. Let us decide on architecture ^_^; — findwindow, Aug 17 '15 at 18:54
(upped) as a reasonable question now that the extra detail has been added — whytheq, Aug 17 '15 at 19:11

whytheq · Accepted Answer · 2015-08-17T19:32:42.043

1

If you need to copy/paste each row then try doing it on the fly as you loop. This is a example that can be adapted:

Sub SelectRowsx()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
Set ws3 = ThisWorkbook.Sheets("Sheet3")

Dim cell
Dim r As Integer
For Each cell In ws1.Range("A1:A3").Cells
    ws2.Rows(2).EntireRow.Copy
    With ws3
       r = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    ws3.Range("A" & r).PasteSpecial xlPasteValues
Next

End Sub

Adapted to your situation:

Sub SelectRowsY()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws3 As Worksheet '<<new: the repository of the row copies

Set wb1 = Workbooks("worksheet1")
Set wb2 = Workbooks("woorksheet2")

Set ws1 = wb1.Sheets("Sheet1")
Set ws2 = wb2.Sheets("Sheet2")
Set ws3 = wb1.Sheets("Sheet3") '<<new: the repository of the row copies


Dim cell
Dim r As Integer
For Each cell In ws1.Range("A1:A650").Cells
    ws2.Rows(cell.Value).EntireRow.Copy
    With ws3
       r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 '<<find last empty row: the place the copy will be pasted
    End With
    ws3.Range("A" & r).PasteSpecial xlPasteValues
Next
End Sub

Seems like it is possible to build up a range made up of disperate rows inside the loop via the method Union - then this range can be copy/pasted to a different location:

Sub SelectRowsT()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet '<<new: the repository of the row copies

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3") '<<new: the repository of the row copies


Dim cell
Dim r As Integer
Dim rn As Range
Dim selRange As Range
For Each cell In ws1.Range("A1:A3").Cells
    Set rn = ws2.Rows(cell.Value).EntireRow
    If (selRange Is Nothing) Then
        Set selRange = rn
    Else
        Set selRange = Union(selRange, rn)
    End If
Next

selRange.Copy
With ws3
   r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 '<<find last empty row: the place the copy will be pasted
End With
ws3.Range("A" & r).PasteSpecial xlPasteValues

End Sub

edited Aug 17 '15 at 19:32

answered Aug 17 '15 at 18:17

whytheq

34,466
65
172
267

Getting a 1004 error 'application-defined or object-defined error' on "ws2.Rows(cell.Value).Select" – Christopher Aug 17 '15 at 18:22
@Christopher - you need to have `ws2` activated to be able to use `Select`. I can edit - although I'm unsure what the point of this code is? – whytheq Aug 17 '15 at 18:33
@Christopher what does this mean? `I want to continually add to my selection instead of just re-selecting the next row?` do you want to `add` numbers up? Do you want to move each row somewhere? If you don't just want to select each one what do you want to do with it? – whytheq Aug 17 '15 at 18:35
You want to have all the disperate rows selected together? – whytheq Aug 17 '15 at 18:36
Yes, there are large gaps, sometimes hundreds if not thousands of rows between selected rows. I want to simulate what would happen if I were to shift+click on the 600 rows I want. – Christopher Aug 17 '15 at 18:38
@Christopher try adapting the example I've provided. Easier to copy/paste on the fly as you execute the loop – whytheq Aug 17 '15 at 19:00
Hmm, still getting the 1004 error as your code is here, and still after adding ws2.Activate to it – Christopher Aug 17 '15 at 19:12
@Christopher I've deleted that initial section of my answer since reading the extra section of your question – whytheq Aug 17 '15 at 19:15
Yes, I see that now. The latest solution worked very well for me. – Christopher Aug 17 '15 at 19:18
@Christopher I think I found an interesting alternative: I'll add to the current answer – whytheq Aug 17 '15 at 19:24
Union probably faster than copy/paste. – findwindow Aug 17 '15 at 19:38

score 1 · Answer 2 · edited May 08 '18 at 21:49

I am going to suggest a more efficient approach, that uses variant arrays.

The strategy is as follows:

Setup the worksheet / workbook objects (as you have done so far)
Store the indexes into a Variant array (named vIndex)
Store the contents into another Variant array (named vContent)
Paste vContent into a range

Here is the code:

Sub CopyPaste()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim vIndex As Variant, vContent As Variant
    Dim lCounter As Long

    ' This is the workbook that contains the index range
    Set wb1 = ThisWorkbook

    ' Or whatever name
    Set wb2 = Workbooks("Other Sheet")

    Set ws1 = Sheet1
    ' First sheet of Workbook2 - change accordingly
    Set ws2 = wb2.Sheets(1)

    ' Store index range to a variant array, for efficiency
    ' I used A2:A25 for my tests, change it to A1:A650
    vIndex = ws1.Range("A2:A25").Value2

    ' Initialize the array to be selected/copied
    ReDim vContent(1 To UBound(vIndex, 1))

    ' Populate the content array, based on the rows shown by the index array
    For lCounter = 1 To UBound(vIndex, 1)
        vContent(lCounter) = ws2.Cells(vIndex(lCounter, 1), 1).EntireRow.Value2
    Next lCounter
' Let's paste it next to another worksheet
For lCounter = 1 To UBound(vIndex, 1)
    wb1.Sheets(2).Range("A" & lCounter).Resize(1, UBound(vContent(1), 2)).Value2 = vContent(lCounter)
Next lCounter
End Sub

EDIT

Edited to copy all row contents (tried and tested).

I strongly suggest you do not copy the entire row, but rather restrict to the space in which you have data.

findwindow · Answer 3 · 2015-08-17T19:09:56.203

0

You're going to need to define lastRow and add a new sheet (ws3) but I hope the logic makes sense. Should add that I am assuming the rows you want are in column A.

For i = 1 To LastRow
'r = row number wanted
r = ws2.Cells(i, 1)
'drop into new sheet
ws3.Rows(i) = ws1.Rows(r)
Next i

edited Aug 17 '15 at 19:09

answered Aug 17 '15 at 18:53

findwindow

3,133
1
13
30

score -1 · Answer 4 · answered Aug 17 '15 at 18:38

In these lines of your code:

For Each Row In ws1.Range("A1:A650").Cells
    ws2.Rows(Row).Select
Next

Row is a range. To get the row number you need Row.Row:

For Each Row In ws1.Range("A1:A650").Cells
    ws2.Rows(Row.Row).Select
Next

This might have been easier to see if you had used a variable name other than Row:

For Each c In ws1.Range("A1:A650").Cells
    ws2.Rows(c.Row).Select
Next

Hope that helps

Select Loop in VBA only selects last row

4 Answers4