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




Reply With Quote

Bookmarks