CODEWITHSUNDEEP
vbaexcel
Münzinger
Münzinger

Reputation: 3

Deleting a row in a sheet when 2 cells in 2 different sheets are the same

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

Upvotes: 0

Views: 262

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

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

Upvotes: 0

Related Questions