Delete Rows When Column Is False

[billgyrotech]

Is there any easy way to delete the rows when the cell value in Column A is False?

Thank you

[p24leclerc]

there is no formula to do this. But you could use Filter from the Data menu that would hide those rows.

If you want to delete them, you'll need a macro. Or you can easily do it manually after sorting your table.

[billgyrotech]

It sometimes is the easy solution that evades us. All I had to do was sort alphabetically on the True/False column and then delete the False rows. Thanks for the tips.

[Richard Buttrey]

As Pierre says a macro would be needed.

An obvious question though is how did the data arrive in this layout where every other row is redundant? If it comes as an import from some other system the sensible way would be to get the output from that system to suppress the rows that aren't needed

[iracknback]

This is a macro I use that works well. for your application you would need to change the FALSE values to blank with a " "

You can use an if statement such as =IF(A1="FALSE"," ",A1) to convert all the FALSE cells to blanks.

Then use this macro.

Sub ClearCell()
    
Application.ScreenUpdating = False
    
    
    Dim rng As Range
    Set rng = ActiveSheet.Range("A1")

    Dim i As Long
    
    For i = 1 To 15000
        
      'clears formula from any cell in range A1:A15000 that returns no value
            If rng.Cells(i, 1) = "" Then
            rng.Cells(i, 1).ClearContents
        
        End If
    
    Next i


'deletes rows that are blank
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True


End Sub

[Richard Buttrey]

...or rather than loop through all cells, which with large ranges can be slow. Just Autofilter the data for the False values, and use the instruction

Range("your_range").SpecialCells(xlCellTypeVisible).EntireRow.Delete.

This is the fastest way of I know of dealing with these sorts of block operations.

[billgyrotech]

Thank you very much for the help

[iracknback]

I see your point in replacing A:A with "your_range" but what does the xlCellTypeVisible do that xlCelltypeblanks does not? I'm very new to this so trying to learn also. Thanks!

[Richard Buttrey]

...or better still

Sub DeleteFalse()
    With Range("A1").CurrentRegion.Offset(1, 0)
        .AutoFilter field:=1, Criteria1:=False
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub

@Irack.

The point is not whether blanks are better or worse than CellTypeVisible. They both have their place but in different circumstances.

The point is that looping through each cell and testing carries a time overhead since with every iteration of the loop, VBA needs to jump back to the Excel App, test a cell and jump back to VBA to perform an action. With large ranges this can take a long time.

With these sorts of things where you need to delete or perhaps copy a specific subset of a data population, the smart thing to do is to first filter the data, then address the whole rage that has been filtered and perform the single action of a block delete specifying the visible cells. i.e. two VBA operations rather than maybe hundreds.

[Richard Buttrey]

..can't seem to edit at the moment.

For the avoidance of doubt your CellTypeBlanks would work equally well in my macro but to get your blanks you've had to process a loop with all the time that takes

[iracknback]

Good info to have. thank you for the assistance in understanding. this is like learning Chinese and knowing how to ask for the same thing 215 different ways......