CODEWITHSUNDEEP
vbaexcel
yashika vaish
yashika vaish

Reputation: 201

Vba code to delete the entire row if value for particular column for that row does not match in another worksheets

I am trying to delete those rows for which values of cells of column A in both worksheet does not match but the given code gives me wrong output and deletes all the data. Kindly help me with the correct version of the code.

Lastrowo = ws1.Cells(Rows.Count, "B").End(xlUp).Row
Lastrowc = ws2.Cells(Rows.Count, "B").End(xlUp).Row

For x = 1 To Lastrowo
  For m = 1 To Lastrowc  
    If Workbooks("A.xlsx").Sheets("Sheet1").Cells(x, 1).Value <>
    Workbooks("B.xlsx").Sheets("Sheet1").Cells(m, 1).Value Then
      Workbooks("A.xlsx").Sheets("Sheet1").Rows(x).EntireRow.Delete
      Workbooks("B.xlsx").Sheets("Sheet1").Rows(m).EntireRow.Delete
    End If
  Next m
Next x

Upvotes: 0

Views: 482

Answers (1)

kolcinx
kolcinx

Reputation: 2233

Same code, without the second loop

Lastrowo = ws1.Cells(Rows.Count, "B").End(xlUp).Row
Lastrowc = ws2.Cells(Rows.Count, "B").End(xlUp).Row

For x = 1 To Lastrowo
    If Workbooks("A.xlsx").Sheets("Sheet1").Cells(x, 1).Value <>
    Workbooks("B.xlsx").Sheets("Sheet1").Cells(x, 1).Value Then
      Workbooks("A.xlsx").Sheets("Sheet1").Rows(x).EntireRow.Delete
      Workbooks("B.xlsx").Sheets("Sheet1").Rows(x).EntireRow.Delete
    End If
Next x

I would recommend reversing the loop by going from bottom to top.
For x = Lastrowo To 1 Step - 1

See other post dealing with similar code: https://stackoverflow.com/a/47062983/4636801

Upvotes: 1

Related Questions