VBA - Remove entire rows when a cell contains -

Spartanjuli1

New Member
Joined
Sep 13, 2016
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
 
Someone else here at Mr. Excel will have to help you.
I did not understand this was inside a Table
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.

Rich (BB code):
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
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
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,

Code:
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
 
Upvote 0
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,

Code:
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,376
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top