Why is my VBA code not iterating to the next i?

Question

Trying to populate a sheet with only instances that meet a criteria. Here the criteria is a 1 in the last column of the dataset.

Current code is only pulling the first iteration. Does not go to next i. Next i in the current dataset is an instance that should be pulled so that is not the issue.

Sub Cleaned()

Dim LastRow As Integer, c As Long, i As Integer, erow As Integer

Worksheets("SPData").Activate
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
c = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To 600
If Cells(i, c) = 1 Then
Range(Cells(i, 1), Cells(i, c)).Select
Selection.Copy
Worksheets("CleanedData").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If

Next i

End Sub

Also tried:

Sub Moving()

Dim r As Long, c As Long, i As Integer, erow As Integer

Worksheets("SPData").Activate
r = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
c = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To r
If Cells(i, c) = 1 Then
Range(Cells(i, 1), Cells(i, c)).Select
Selection.Copy
Worksheets("CleanedData").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste

End If

Next i

End Sub

You need to read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba You never re-activate your original sheet. — SJR, Aug 12 '19 at 17:41

Danny Papadopulos · Answer 1 · 2019-08-12T20:44:43.477

1

Correct me if I am wrong - you want to copy the entire row if the value in the last column is equal to 1?

If so then this code works:

    Sub Moving()

    Dim r As Long
    Dim c As Long
    Dim i As Integer
    Dim erow As Integer

    With Worksheets("SPData")
        r = .Cells(.Rows.Count, 2).End(xlUp).Row
        c = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For i = 2 To r
            If .Cells(i, c) = 1 Then
                .Range(.Cells(i, 1), .Cells(i, c)).Copy

                With Worksheets("CleanedData")
                    erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                    .Paste Destination:=.Cells(erow, 1)
                End With
            End If
        Next i
    End With
End Sub

I would strongly advise you to avoid using .Select in VBA whenever you can.

Also it is usually much better to refer to the actual sheet rather than using ActiveSheet.

edited Aug 12 '19 at 20:44

answered Aug 12 '19 at 19:43

Danny Papadopulos

467
1
5
14

1

Good advice to avoid ActiveSheet,. But your code still uses it. – chris neilsen Aug 12 '19 at 20:06
1

I took the liberty of fixing it for you. Hope you don't mid (if you _do_ you can roll back the change) – chris neilsen Aug 12 '19 at 20:23
Thank you Chris, however removing the End With is not a good call, as further down inside of the loop there is another With End With, now the code actually doesn't work. But after moving it back, with the rest of 'dots' that you added for sheet reference - everything works – Danny Papadopulos Aug 12 '19 at 20:33
with your version you will get a complie error, because there is a `.Something` outside a `With` block. – chris neilsen Aug 12 '19 at 20:41
1

Nesting `With` blocks works fine. when you come out of the inner `With`, the dots once again refer to the outer one – chris neilsen Aug 12 '19 at 20:42

Why is my VBA code not iterating to the next i?

1 Answers1