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

 

[My Aswer Is This]

Someone else here at Mr. Excel will have to help you.
I did not understand this was inside a Table

 

[Peter_SSs]

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" ...

Assuming that table starts in cell A1, try this code in a copy of your workbook.

If the table is not called "Table1", edit the code where indicated.

Sub DelRws()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, nc As Long
 
  Const ColToCheck = 88
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  a = Range(Cells(2, ColToCheck), Cells(Rows.Count, ColToCheck).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If LCase(a(i, 1)) = "false" Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("Table1")                '<- Check table name
      nc = .Columns.Count + 1
      With .Resize(, nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        .Resize(k).EntireRow.Delete
        .Columns(nc).Delete
      End With
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
  End If
End Sub

 

[My Aswer Is This]

So Peter I have a question.
When I provide scripts using loops people here say loops are slow use filters.
But here your using a loop. I'm comfused.

Assuming that table starts in cell A1, try this code in a copy of your workbook.

If the table is not called "Table1", edit the code where indicated.

Sub DelRws()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, nc As Long
 
  Const ColToCheck = 88
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  a = Range(Cells(2, ColToCheck), Cells(Rows.Count, ColToCheck).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If LCase(a(i, 1)) = "false" Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("Table1")                '<- Check table name
      nc = .Columns.Count + 1
      With .Resize(, nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        .Resize(k).EntireRow.Delete
        .Columns(nc).Delete
      End With
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
  End If
End Sub

 

[My Aswer Is This]

Peter:

To do what the user wanted when not part of a Table was a simple little script like I provided.
Is it not possible to use a simple script like mine to filter date inside a Table.

 

[Spartanjuli1]

Hello,

Thank you all for your help, I managed to make it worked on my side for what I was looking for, only flaw is when the query has finished and you click on the button again, I have an Error 400 but that's normal as there is no new data to delete.

Best Regards,

Sub Delete_rows_in_a_table_with_particular_data()




Dim RngToDelete As Range
Range("Table1").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=88, Criteria1:="FALSE"
    Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
    Selection.AutoFilter
    RngToDelete.Delete
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=88
  
    
End Sub

 

[My Aswer Is This]

Thats great now see I learned something. I had never tried to write a script to delete rows in a Table.
I removed the last line of code and the error did not occure.

See I can understand simple scripts like this.

Hello,

Thank you all for your help, I managed to make it worked on my side for what I was looking for, only flaw is when the query has finished and you click on the button again, I have an Error 400 but that's normal as there is no new data to delete.

Best Regards,

Sub Delete_rows_in_a_table_with_particular_data()




Dim RngToDelete As Range
Range("Table1").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=88, Criteria1:="FALSE"
    Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
    Selection.AutoFilter
    RngToDelete.Delete
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=88
  
    
End Sub

 

[Peter_SSs]

So Peter I have a question.
When I provide scripts using loops people here say loops are slow use filters.
But here your using a loop. I'm comfused.

It depends on the circumstances. Looping through 20,000 worksheet rows would be quite slow compared to looping through 20,000 items of data that have been read into memory from the worksheet in a single go. Try setting up some sample data with that may rows with "False" values scattered throughout column 88 and run the two codes and you should see the difference.

Peter:

To do what the user wanted when not part of a Table was a simple little script like I provided.
Is it not possible to use a simple script like mine to filter date inside a Table.

It probably is, but with large data (20,000 rows) codes to do the job could take 20 seconds or more or they could take an extremely short time. I wa slooking for the latter as I thought it would suit the OP better.

..I managed to make it worked on my side for what I was looking for,..

That's good that you have a working solution. However, given my comments above, I am wondering about how many rows might have needed deleting and how quickly the rows got deleted with your code? Did you try my code too?

 

[My Aswer Is This]

Thanks for your comments Peter.
I never test scripts for speed. I write scripts I understand.
And if yours are faster that's great. And I know my tag line says:
"Always trying to learn more"
But I have no desire to try and figure out scripts like some of yours they are just way over my head.
But I'm glad your here always providing other ways to do things. Take care

It depends on the circumstances. Looping through 20,000 worksheet rows would be quite slow compared to looping through 20,000 items of data that have been read into memory from the worksheet in a single go. Try setting up some sample data with that may rows with "False" values scattered throughout column 88 and run the two codes and you should see the difference.


It probably is, but with large data (20,000 rows) codes to do the job could take 20 seconds or more or they could take an extremely short time. I wa slooking for the latter as I thought it would suit the OP better.



That's good that you have a working solution. However, given my comments above, I am wondering about how many rows might have needed deleting and how quickly the rows got deleted with your code? Did you try my code too?