Delete entire row based on cell value

Question

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

(1) always delete from last to first (because deleting will change row numbers of following rows. (2) Use the debugger and step thru the code step by step to check where the code behaves different than you expect. — FunThomas
– FunThomas, Commented Aug 17, 2020 at 12:11
@Dorian: No, don't do that. Avoid modifying the counter of a loop. Always delete backwards, else code gets easily confusing — FunThomas
– FunThomas, Commented Aug 17, 2020 at 12:27
Just a comment, of course work backwards. But the loop looks to stop when a cell in column 4 is not blank. Possibly there are cells that are not blank in column 4. — Davesexcel
– Davesexcel, Commented Aug 17, 2020 at 12:33
Best way to loop backwards would be to count the rows and use the rowcount as the loop — Davesexcel
– Davesexcel, Commented Aug 17, 2020 at 12:35

Damian · Accepted Answer · 2020-08-17 16:54:39Z

1

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

edited Aug 17, 2020 at 16:54

answered Aug 17, 2020 at 12:36

Damian

5,1741 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Turner Over a year ago

I've tried to run this code in place of the loop however "LastRow = .Cells(.Rows.Count, 4).End(xlUp)" brings up the debugger. I am looking to use a loop similar to my proposed one for simplicity and consistency with the rest of my code - is there anyway this can be done using a loop like my proposed one?

Damian Over a year ago

@Turner sorry, now it should work. The .Row was missing, so the code was trying to assign a Range to a Long variable. Now it's giving a Long type.

Turner Over a year ago

Ah I see this is great - works a treat and I never would have though of this. I've applied this directly to another area of my code but the argument is now: LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row .Cells(1, 12) = "Dupes Checker" For Each cell In .Range(.Cells(2, 4), .Cells(LastRow, 4)) If .Cells(cell.Row, 12).Value = "TRUE" Then .Cells(Cells.Row, 12) = "UNIQUE" The rest remains unchanged but won't run

Damian Over a year ago

Glad I could help @Turner, if this helped you please consider to mark this answer as correct.

Collectives™ on Stack Overflow

Delete entire row based on cell value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related