Add and/sum/offset condition to row delete code-CodePudding

I have the code below, which deletes all rows for which the value in Column C is not a named range ("ReferenceLocations").

' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long, TestRange As Range, MyRange As Range

'DELETE IRRELEVANT LOCATIONS
' Defines LastRow as the last row of data based on column C
LastRow = Sheets(Sheet1).Cells(Rows.Count, "C").End(xlUp).Row


' Sets check range as C1 to the last row of C
Set cRange = Range("C2:C" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the values listed in "ReferenceLocations" named range, delete rows
        If IsError(Application.Match(.Value, Range("ReferenceLocations"), 0)) Then
   .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x

My goal now is to add an And condition to the "IsError...Then" line, which will sum the values of cells to the right from column (X) to Column P, with the (X) given by the value in a cell on another sheet (probably an offset). The function should only delete the entire row if the condition in code above is met AND the sum of the cells in column (X) to P = 0.

CodePudding user response：

To get the value of the sum, we just need to give a range to the SUM formula. We know that we're starting from a cell, some columns to the right of cRange.Cells(x) which can be written as cRange.Cells(x).Offset(, y) which would be something like "K5" if x was 4 and y was 8. The second cell we want to get to is cRange.Cells(x).EntireRow.Cells(16) which would be "P5" if x was 4. So we can just use the Range function to create a range that stretches from one cell to the other. Range(cRange.Cells(x).Offset(, y), cRange.Cells(x).EntireRow.Cells(16)). This would be "K5:P5" if x was 4 and y was 8.

To make use of the With statement, and to not repeat the object inside the with block, we can rephrase it as .Parent.Range(.Offset(, y), .EntireRow.Cells(16))

    If IsError(Application.Match(.Value, Range("ReferenceLocations"), 0)) _
    And WorksheetFunction.Sum(.Parent.Range(.Offset(, y), .EntireRow.Cells(16))) = 0 _
    Then
       .EntireRow.Delete
    End If