VBA - Delete Rows in named range if value is False

[tttommy2]

Hi there

I have a named range ("RRR") which consists of 1 column and each cell either is TRUE or FALSE. I want to delete the rows which have FALSE.

I tried the code below, however it doesn't delete all the rows with FALSE.

However a curious thing is that if I run the code several times (in Excel 2010) it will finally delete all the FALSE rows!

I'd like my code to work in 1 swoop. Any ideas please?

Sheets(1).Range("$E$4:$E1245").Name = "RRR"

Dim Jcell As Range
    
For Each Jcell In Range("RRR")
If Jcell.Value = False Then Jcell.EntireRow.Delete
Next

 

[Joe4]

The issue is that as you move down a column and delete rows, it shifts the rows up. So if you have 2 FALSE entries in a row, it will miss the second. So you need to loop through your range backwards to avoid those two things form passing each other in reverse direction.

Here is some code that will dynamically find the last row with data in column E (to avoid unnecessary checks):

Sub MyDeleteRows()

    Dim lastRow As Long
    Dim myRow As Long

    Application.ScreenUpdating = False

'   Find last row in column E
    lastRow = Sheets(1).Cells(Rows.Count, "E").End(xlUp).Row

'   Loop through all rows in column E backwards deleting FALSE rows
    For myRow = lastRow To 4 Step -1
        If Sheets(1).Cells(myRow, "E") = False Then Sheets(1).Rows(myRow).Delete
    Next

    Application.ScreenUpdating = True

End Sub

 

[tttommy2]

Thank you Joe4. Your code works perfectly for me and good explanation of why mine don't.

The issue is that as you move down a column and delete rows, it shifts the rows up. So if you have 2 FALSE entries in a row, it will miss the second. So you need to loop through your range backwards to avoid those two things form passing each other in reverse direction.

Here is some code that will dynamically find the last row with data in column E (to avoid unnecessary checks):

Sub MyDeleteRows()

    Dim lastRow As Long
    Dim myRow As Long

    Application.ScreenUpdating = False

'   Find last row in column E
    lastRow = Sheets(1).Cells(Rows.Count, "E").End(xlUp).Row

'   Loop through all rows in column E backwards deleting FALSE rows
    For myRow = lastRow To 4 Step -1
        If Sheets(1).Cells(myRow, "E") = False Then Sheets(1).Rows(myRow).Delete
    Next

    Application.ScreenUpdating = True

End Sub