CODEWITHSUNDEEP
excelvba
Turner
Turner

Reputation: 15

Delete entire row based on cell value

I am trying to delete row based upon their values (i.e. if a cell contains the word DELETE) then the entire row should be deleted and shifted up.

I currently have code that loops through data and applies the cell value "IN-SCOPE" or "DELETE" to column 11 depending on the date present in Column 4. This works fine - however, the code I've written to delete any items labeled with "DELETE" doesn't do anything. Below is the code I currently have - any help would be great.

'Loop that lables items as in-scope IF they fall within the user defined range
    y = 2
    
StartDate = Controls.Cells(15, 10).Value
EndDate = Controls.Cells(15, 11).Value

    Bracknell.Activate
    Cells(1, 11).Value2 = "Scope Check"
        Do While Cells(y, 4).Value <> ""
            If Cells(y, 9).Value >= StartDate And Cells(y, 9).Value < EndDate Then
            Cells(y, 11).Value = "IN-SCOPE"
            Else: Cells(y, 11).Value = "DELETE"
        End If
    y = y + 1
    Loop

'Loop to delete out of scope items

    Bracknell.Activate
    
    z = 1

    Do While Cells(z, 4).Value <> ""
        If Cells(z, 11).Value = "DELETE" Then
        Range("A" & z).EntireRow.Delete shift:=xlUp
    End If
    z = z + 1
    Loop

Upvotes: 0

Views: 241

Answers (1)

Damian
Damian

Reputation: 5174

Try this, the code is self explained:

Option Explicit
'use option explicit to force yourself
'to declare all your variables
Sub Test()
    
    'Loop that lables items as in-scope IF they fall within the user defined range
    Dim StartDate As Date
    StartDate = Controls.Cells(15, 10).Value
    Dim EndDate As Date
    EndDate = Controls.Cells(15, 11).Value
    
    With Bracknell
        'Instead deleting every row, store them into a range variable
        Dim RangeToDelete As Range
        'Calculate your last row with data
        Dim LastRow As Long
        'Assuming your column 4 has data on all the rows
        'If not, change that 4 for a column index that has data.
        LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
        'The most efficient way to loop through cells
        'is using For Each loop
        Dim cell As Range
        .Cells(1, 11) = "Scope Check"
        'loop through every row in column 4
        For Each cell In .Range(.Cells(2, 4), .Cells(LastRow, 4))
            'if the cell of that row in column 9 is between
            If .Cells(cell.Row, 9) >= StartDate And .Cells(cell.Row, 9) < EndDate Then
                .Cells(cell.Row, 11) = "IN-SCOPE"
            Else
            'if not, check if rangetodelete is empty
                If RangeToDelete Is Nothing Then
                    'if it is empty, set it as the cell
                    Set RangeToDelete = cell
                Else
                    'if not, set it as what it already is and the new cell
                    Set RangeToDelete = Union(RangeToDelete, cell)
                End If
            End If
        Next cell
        'Once you ended the loop you'll get the variable
        'with every cell that didn't meet your criteria
        'Check if is nothing, which means there are no cell to delete
        If Not RangeToDelete Is Nothing Then RangeToDelete.EntireRow.Delete
    End With

End Sub

Upvotes: 1

Related Questions