Try this
Code:
Sub conditionally_delete_entire_row() ' http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row
Rem 1 workbooks, Worksheets info ' only macro.xlsm is open so we have to open that file
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Workbooks.Open Filename:=ThisWorkbook.Path & "\1.xlsx"
Set Wb1 = ActiveWorkbook
Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of Sheet1
Set Wb2 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\2.xlsx")
Set Ws2 = Wb2.Worksheets.Item(1) ' worksheet of first tab
'1b Ranges
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Ws1.Range("A1").CurrentRegion: Set Rng2 = Ws2.Range("A1").CurrentRegion
Dim Rng1A As Range, Rng2A As Range
Set Rng1A = Rng1.Range("A1:A" & Rng1.Rows.Count & ""): Set Rng2A = Rng2.Range("A1:A" & Rng2.Rows.Count & "")
Rem 2 take each cell in column A of 1.xlsx and compare it with each cell in column A of of 2.xlsx
Dim RngS1 As Range, RngS2 As Range
For Each RngS1 In Rng1A ' each cell in column A of 1
For Each RngS2 In Rng2A ' each cell in column A of 2
If RngS1.Value = RngS2.Value Then ' compare .. if column A of 1.xlsx matches with column A of of 2.xlsx
Dim DtaClc As Long ' calculate the data in that row
Let DtaClc = Application.WorksheetFunction.Sum(RngS2.Resize(1, Rng2.Columns.Count)) ' https://docs.microsoft.com/de-de/office/vba/api/excel.worksheetfunction.sum
If DtaClc > 0 Then ' delete all the numbers from that entire row (numbers is starting from column B)
'RngS2.Offset(0, 1).Resize(1, Rng2.Columns.Count - 1).Delete shift:=xlToLeft
RngS2.Offset(0, 1).Resize(1, Rng2.Columns.Count - 1).ClearContents
Else
' Dtaclc is negative then do nothing
End If
Else
' column A of 1.xlsx does not match with column A of of 2.xlsx - do nothing
End If
Next RngS2
Next RngS1
End Sub
Bookmarks