How to paste a row in VBA

Question

I am trying to cut and paste a selected row to the very last row of the data.

I am doing this by selecting a row featured in a listbox. If I click CommandButton3, it is supposed to cut the corresponding row of data and paste it to the very last row of the cells.

This is my code (which is not working):

Private Sub CommandButton3_Click()

Dim i As Integer

For i = 1 To Range("A10000").End(xlUp).Row

    If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Select
        Selection.Cut
        Cells(Range("A10000").End(xlUp).Row + 1, 1).Select
        Selected.Paste
    End If

Next i

End Sub

I don't understand why Selected.Paste line does not work. Am I missing something here?

score 1 · Accepted Answer · 2018-04-23T02:17:39.737

1

There is no such thing as Selected. If Select is absolutely necessary, use Selection for a range of cells or ActiveCell when the target is a single cell.

You can discard Select in favor of more direct cell referencing almost all of the time.

For i = 1 To Range("A10000").End(xlUp).Row

    If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Cut destination:=Cells(rows.count, "A").End(xlUp).offset(1, 0)
    End If

Next i

When you are ready to advance to the next level, please read How to avoid using Select in Excel VBA.

edited Apr 23 '18 at 02:17

answered Apr 23 '18 at 00:23

Thanks for the answer! I used Selection.Paste and the error message says that "Object doesn't support this property or method" should I declare Cells... as some object and then use Selection... line? – Subin Park Apr 23 '18 at 00:49
Removed use of Select altogether above. – Apr 23 '18 at 02:19

Harun24hr · Answer 2 · 2018-04-23T13:48:54.977

1

Selected is not a valid method. Use Activesheet rather than selected. Like ActiveSheet.Paste

So, full code is below.

Sub CommandButton3_Click()
Dim i As Integer

For i = 1 To Range("A10000").End(xlUp).Row

    If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Select
        Selection.Cut
        Cells(Range("A10000").End(xlUp).Row + 1, 1).Select
        ActiveSheet.Paste
    End If

Next i
End Sub

edited Apr 23 '18 at 13:48

answered Apr 23 '18 at 02:21

Harun24hr

30,391
4
21
36

But you should avoid use of `select`. – Harun24hr Apr 23 '18 at 13:49

How to paste a row in VBA

2 Answers2