Next without for error in excel vba in loop

Question

Struggling with a bit of code that's doing my head in - I'm trying to compare two worksheets and delete duplicate rows based on all the information presented. The ideal structure would be PasteCSV is compared to OriginalCSV. The macro checks for duplicate rows and then deletes the row if all the data matches - I'm attempting to pull this off with if statements, but not 100% sure if I'm doing this correctly:

Sub DeleteDuplicates()
Dim Row As Long
Dim Vendor As Range
Dim Software As Range
Dim Version As Range

Sheets("PasteCSV").Select

Columns("A").Delete

For Row = Range("A65536").End(xlUp).Row To 1 Step -1

    Set Vendor = Sheets("OriginalCSV").Range("A").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not Vendor Is Nothing Then
    Set Software = Sheets("OriginalCSV").Range("B").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not Software Is Nothing Then
    Set Version = Sheets("OriginalCSV").Range("C").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not Version Is Nothing Then
    Cells(Row, 1).EntireRow.Delete

End If

Next Row
Sheets("PasteCSV").Cells.Copy

Sheets(Sheets.Count).Select

Range("A1").Select


ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Any help would be much appreciated!

I think, the error message leads you wrong. You are missing two End If as every If needs his own. — IQV
– IQV, Commented Sep 5, 2017 at 6:57
You are missing two end if's. Or you should replace your second and third IF with Else. — Luuklag
– Luuklag, Commented Sep 5, 2017 at 6:57

Rowen Chumacera · Accepted Answer · 2017-09-05 07:16:55Z

1

To better explain the use of If statements in VBA..

If you want to avoid using End If and you only have one line to execute if the condition is true, just put the process statement on the same line as your If or nested If conditions.

Example:

If x > y Then MsgBox z

If you want to clearly see your process statement, or you have multiple processing statements, if the condition is true, then you need to use End If for each corresponding If condition.

Examples:

If x > y Then
  MsgBox z
End If

If x > y Then
  MsgBox x
  MsgBox y
  MsgBox z
End If

If x > y Then
  MsgBox x
Else
  MsgBox y
End If

If x > y Then
  MsgBox x
Else If x < y Then
  MsgBox y
Else
  MsgBox z
End If

answered Sep 5, 2017 at 7:16

Rowen Chumacera

5293 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IQV · Accepted Answer · 2017-09-05 07:02:12Z

0

I think, the error message leads you wrong. You are missing two End If as every If needs his own:

For Row = Range("A65536").End(xlUp).Row To 1 Step -1

    Set Vendor = Sheets("OriginalCSV").Range("A").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
    If Not Vendor Is Nothing Then
        Set Software = Sheets("OriginalCSV").Range("B").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
    End If
    If Not Software Is Nothing Then
        Set Version = Sheets("OriginalCSV").Range("C").Find(Cells(Row, 1), LookIn:=xlValues, lookat:=xlWhole)
    End If
    If Not Version Is Nothing Then
        Cells(Row, 1).EntireRow.Delete
    End If

Next Row

answered Sep 5, 2017 at 7:02

IQV

5006 silver badges14 bronze badges

Comments

Ivan Tokarev · Accepted Answer · 2017-09-05 08:33:25Z

0

another way is to put your "if...then" into one line like this:

If Not Version Is Nothing Then Cells(Row, 1).EntireRow.Delete

answered Sep 5, 2017 at 8:33

Ivan Tokarev

1016 bronze badges

Comments

Moosli · Accepted Answer · 2017-09-06 07:53:43Z

0

If you are Delting Rows, you have to go Button up. You can see how to do this in the Code Bellow

Here is the Code with the Changes:

Sub DeleteDuplicates()
Dim Row As Long
Dim rng As Range
Dim rng2 As Range
Dim rngSearch As Range
Dim Vendor As Range
Dim Software As Range
Dim Version As Range


Sheets("PasteCSV").Select
Columns("A").Delete
Row = Cells(Rows.Count, 1).End(xlUp).Row

For I = Row To 1 Step -1

    Set Vendor = Sheets("OriginalCSV").Columns(1).Find(Range("A" & I).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not Vendor Is Nothing Then
        If Vendor.Offset(0, 1).Value = Range("B" & I).Value And _
            Vendor.Offset(0, 2).Value = Range("C" & I).Value Then
            Rows(I).EntireRow.Delete
        End If

    End If
Next I

Sheets("PasteCSV").Cells.Copy
Sheets(Sheets.Count).Select

Range("A1").Select


ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

edited Sep 6, 2017 at 7:53

answered Sep 5, 2017 at 7:13

Moosli

3,2853 gold badges22 silver badges48 bronze badges

3 Comments

siliconphoenix Over a year ago

Thanks all - tried all the examples, but no dice! Moosil - running your code now gives me an Invalid Qualifer on rng.Row.EntireRow.Delete Modifying my existing code with End If statements still returns the 'Next without For' issue.... sigh Any more ideas?

Moosli Over a year ago

@siliconphoenix ok Updated the Code. Now it should work.

siliconphoenix Over a year ago

Still nothing - the code doesn't define 'i' (which I corrected with Dim i as Long) - but this just leads to it directly copying the data instead of deleting the duplicates. As it stands, I need the code to check to see if Column A, B and C match. If so, delete the whole row. I've tried multiple options in my research, but your code seems to be getting the closest

Collectives™ on Stack Overflow

Next without for error in excel vba in loop

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related