Trying to Delete Rows with False Value in my Range

Question

I'm pretty sure I'm nearly there. At the moment this deletes all rows. I just want it to delete the rows identified as having the word FALSE in them.

Dim R As Range
Dim myRange As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Set myRange = Range("U2:U" & LastRow)

For Each R In myRange
    If R.Value = "FALSE" Then
        Rows.Delete
    End If
Next

I've also tried using the below code but it did not help.

Dim R As Range
Dim myRange As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Set myRange = Range("U2:U" & LastRow)

For Each R In myRange
    If R = "False" Then
        R.EntireRow.Delete
    End If
Next```

Here's a screenshot of how the value is stored. It's was a field that was stripped from a Vlookup and converted to a value.

Does this answer your question? [Clear Cell Contents of range if Contes = 0](https://stackoverflow.com/questions/64095962/clear-cell-contents-of-range-if-contes-0) — JMP, Sep 29 '20 at 04:23
No It doesn't. I'll add to the original post, also what I've tried with that. Sorry. — Campbell, Sep 29 '20 at 04:29
Does this answer? [Delete row based on partial text](https://stackoverflow.com/questions/11317172/delete-row-based-on-partial-text) No need to loop. You can use `Autofilter` to delete the relevant rows in 1 go. — Siddharth Rout, Sep 29 '20 at 04:52
BTW how is the "False" stored in the cell? As a String or as a Boolean value? Perhaps you can add a screenshot? — Siddharth Rout, Sep 29 '20 at 05:00
@SiddharthRout I reworked your "Delete row based on partial text" and it looks like it is doing something, and doesn't error, but doesn't actually delete the FALSE rows. — Campbell, Sep 29 '20 at 05:14

score 1 · Accepted Answer · answered Sep 29 '20 at 05:17

Working with False can be tricky so I created an example for you as shown below.

As I recomendded in the comments, do not use a loop. Use Autofilter as mentioned in Delete row based on partial text

Try this

Code:

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim SearchValue As Boolean
    Dim lRow As Long

    SearchValue = False
    
    '~~> Change this to the relevant sheet
    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range("U1:U" & lRow)
          .AutoFilter Field:=1, Criteria1:=SearchValue
          .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

Final result:

Note:

If this works for you, I will close this question as a duplicate but retain this answer as it shows how to deal with Boolean and Boolean (Stored as string) values.

I appreciate this approach as this is faster than others answer. — Harun24hr, Sep 29 '20 at 05:24
@Siddharth Fantastic - this worked! Really appreciate your time! — Campbell, Sep 29 '20 at 05:38

score 0 · Answer 2 · answered Sep 29 '20 at 04:46

0

Try This:

Sub DeleteRow()

    Dim LastRow As Long, cell As Range
    LastRow = ThisWorkbook.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In ThisWorkbook.ActiveSheet.Range("U2:U" & LastRow)
        If cell.Value = "False" Then
        cell.EntireRow.Delete
        End If
    Next

End Sub

Note: ThisWorkbook.ActiveSheet is not necessary it is better to give proper reference.

answered Sep 29 '20 at 04:46

Amit

36
4

Sorry @Amit This does not work either. – Campbell Sep 29 '20 at 04:52
If you share your Excel File with Dummy Data I can do my best. – Amit Sep 29 '20 at 05:06
Or you can also Try this with replacing the "False" with "FALSE" or False. – Amit Sep 29 '20 at 05:14
@Amit Your answer will fail because you need to loop through from bottom to top as your are deleting row. After deleting rows bottoms rows shift to up. – Harun24hr Sep 29 '20 at 05:26
1

@Harun24HR Sorry I Forgot that. – Amit Sep 29 '20 at 05:48

score 0 · Answer 3 · answered Sep 29 '20 at 05:22

0

Have a try on below sub.

Sub RemoveWithFalse()
Dim rng As Range, rws As Long, i As Long
Dim LastRow As Long

  LastRow = Cells(Rows.Count, "U").End(xlUp).Row
  Set rng = ActiveSheet.Range("U1:U" & LastRow)
  rws = rng.Rows.Count
  
  For i = rws To 1 Step (-1)
    If rng.Rows(i) = False Then rng.Rows(i).EntireRow.Delete
  Next
  
  Set rng = Nothing
  
End Sub

If Column U is formatted as Text then use below line

If rng.Rows(i) = "FALSE" Then rng.Rows(i).EntireRow.Delete

answered Sep 29 '20 at 05:22

Harun24hr

30,391
4
21
36

this was close, but deleted any rows containing FALSE. Changing to the text format didn't work either. – Campbell Sep 29 '20 at 05:38
Then you need to use bottom most line. – Harun24hr Sep 29 '20 at 05:46

Trying to Delete Rows with False Value in my Range

3 Answers3

Linked