0

This macro should compare cells in 2 different rows in 2 worksheets in the same workbook. When the value of the cells matches the row of the cell with the same value in a third worksheet same workbook should get deleted.

In the sheets that get compared are formulas but not in the sheet were the rows should be deleted.

The Problem is that it does not delete the cells in the third sheet. I inserted pieces of code that suggest me that the comparison works. But the code seems to randomly delete stuff.

I tried solutions from the following (and some others were i dont have the links anymore) links:

How to compare columns from two different excel workbooks

Compare 2 cells in different sheets in VBA(Excel 2010)

Sub Vergleich_alter_Kundenausdruck_mit_aktuellen_Werten_V3()

Application.ScreenUpdating = False

Dim Sht1LastRow As Long
Dim i As Long
Dim j As Long

Set s1 = Sheets("Konditionen")
Set s2 = Sheets("Konditionseingabeausdruck")
Set s3 = s2.Next

Sht1LastRow = Sheets("Konditionen").Cells(65536, 1).End(xlUp).Row

For i = Sht1LastRow To 15 Step -1        
  If IsEmpty(s1.Cells(i, 6)) = True Then GoTo leer:                        
  If s3.Cells(i, 5).Value <> s1.Cells(i, 6).Value Then
    s1.Cells(i, 10).Value = s3.Cells(i, 5).Value       
  ElseIf s3.Cells(i, 5).Value = s1.Cells(i, 6).Value Then
    s2.Cells(i, 6).EntireRow.Delete
    s1.Cells(i, 9).Value = s3.Cells(i, 5).Value
  Else                        
    GoTo leer:
  End If            
leer:              
Next i        

Application.ScreenUpdating = True

End Sub
niton
  • 8,771
  • 21
  • 32
  • 52
Münzinger
  • 3
  • 3
  • 3
    When you are deleting rows, you need to step backwards thru the rows, or it just turns into a mess. – braX Aug 08 '17 at 12:14
  • Thank you for the answer braX! I changed for i=... to the way Egan Wolf posted below and now i am trying to get it to work. – Münzinger Aug 08 '17 at 13:11
  • I edited my question with the solution i got with implementing the backwards counting from Egan Wolf and braX. That alone did not fix my problem but put me on the right track. I switched from using a second variable j to just using i. The code has still some issues but i got over my dead Point! Thanks to you both. – Münzinger Aug 08 '17 at 14:15

1 Answers1

0

In case of using the code:

Dim i As Long
With Workbooks("Book1").Worksheets("Sheet1")
    For i = 1 To 4
        If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete
    Next i
End With

You will miss some rows. For example, if the code deletes row 3, then row 4 becomes row 3. However, variable i will change to 4. So, in this case the code will miss one row and check another, which wasn't in range previously.

In your case you should use this

For i = Sht1LastRow To 15 Step -1
Egan Wolf
  • 3,533
  • 1
  • 14
  • 29
  • Hello Egan Wolf, thank you for the fast answer! I am currently trying to implement your suggestions. I can not delete rows in s1/sheet1 because that is a document getting send to the customer. On the other hand i switched to your second Piece of code for i=.... It still does not work BUT i still need to look at what needs to get changed as well since i am now counting backwards for i variable. After i tried a bit i will tell you what happend! – Münzinger Aug 08 '17 at 13:05