VBA - Remove entire rows when a cell contains -

[Spartanjuli1]

Hello,

This is a topic I was looking on google but every VBA do not work for me and I don't know what I am doing wrong unfortunately.
I am working under Excel 2013.

To make it simple, I have a similar tab below where I want to delete the entire row where there is the value "FALSE" in the B column and shift up other rows.
This table is pretty big and values from columns B are the result of a Formula (I do not know if that will impact the VBA)

Do you know a query that would work here ?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]x[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]v[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]x[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]50618[/TD]
[TD]v[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


To become

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]v[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]x[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]xv[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your help !
Julien

 

[Trebor76]

Hi Spartanjuli1,

Run the following macro while on the sheet in question (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro2()

    Const lngStartRow As Long = 1 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(B" & lngStartRow & "=FALSE,NA(),"""")"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. B that were flagged with FALSE have now been deleted.", vbInformation

End Sub

Note as it doesn't loop it's very fast.

Robert

 

[njimack]

Hi Spartanjuli1,

Run the following macro while on the sheet in question (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro2()

    Const lngStartRow As Long = 1 'Starting data row number. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(B" & lngStartRow & "=FALSE,NA(),"""")"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows from Col. B that were flagged with FALSE have now been deleted.", vbInformation

End Sub

Note as it doesn't loop it's very fast.

Robert

Out of interest, how does this compare speed-wise to using autofilter on column B and using SpecialCells.(xlCellTypeVisible)?

 

[My Aswer Is This]

Try this:

Sub Filter_Me()
Application.ScreenUpdating = False
    With ActiveSheet.Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))
        .AutoFilter Field:=1, Criteria1:="False"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

 

[Spartanjuli1]

Try this:

Sub Filter_Me()
Application.ScreenUpdating = False
    With ActiveSheet.Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))
        .AutoFilter Field:=1, Criteria1:="False"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

Hello,

This works great with the example I have above and with my file that's good until the line.
.AutoFilter Field:=1, Criteria1:="False"

but at the time to execute the next one, I have the run time error 1004.
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

in my file, the column where the "FALSE" value is located is "CJ", so column 88.
I modified the Autofilter field to "88" but it is not enough.

How can it be done then ?

Thank you

 

[My Aswer Is This]

Try this:

You will see in row 1 of the code I entered 88 in three different places. Leaving filter set to 1

Sub Filter_Me()
Application.ScreenUpdating = False
    With ActiveSheet.Range(Cells(1, [COLOR=#ff0000]88[/COLOR]), Cells(Cells(Rows.Count, [COLOR=#ff0000]88[/COLOR]).End(xlUp).Row, [COLOR=#ff0000]88[/COLOR]))
        .AutoFilter Field:=1, Criteria1:="False"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

This table is pretty big ...

How big (about)?

 

[Trebor76]

Out of interest, how does this compare speed-wise to using autofilter on column B and using SpecialCells.(xlCellTypeVisible)?

Good question. It would depend on the size of the data-set I suppose. There is a limit to the number of the rows that can be filtered but we'd be talking thousands of rows.

Maybe Spartanjuli1 could try both and let us know?

 

[My Aswer Is This]

If your value to search for and your column to look in will change.
You will get Inputbox asking for these criteria
Try this:

Sub Filter_Me()
Application.ScreenUpdating = False
On Error GoTo M
Dim ans As Long
ans = InputBox("Enter filter column like 88")
anss = InputBox("Enter filter value Like Apples")
    With ActiveSheet.Range(Cells(1, ans), Cells(Cells(Rows.Count, ans).End(xlUp).Row, ans))
        .AutoFilter Field:=1, Criteria1:=anss
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We found no rows meeting this request"
ActiveSheet.AutoFilterMode = False
End Sub

 

[Spartanjuli1]

Hello,

My table has 20,000 rows and 88 columns with data.
I am a newbie with VBA and I believe I did not gave you an important data: the column to filter is included into a "Table" and when I run your script with the data out of the table, it works but not when it is under a "Table"...

Sorry for this, I believe this has an impact then on the script.

Best Regards