Delete Rows that contain X in column Y?

[PsYiOn]

Im looking to delete all rows on my spreadsheet that do not have a particular value in column D. I have no code so far by i suspect i need some kind of for loop?

any help?

Regards

PsYiOn

[Bob Phillips]

This should get you started



Public Sub DeleteRowsUsingAutofilter()
Const TestColumn As Long = 4
Dim cRows As Long
'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
'finally, apply the autofilter for al matching cells
Columns(TestColumn).AutoFilter Field:=1, Criteria1:="some value", Operator:=xlAnd
'we now have only matching rows visible, so we can
'delete these matching rows
With Cells(2, TestColumn).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Columns(TestColumn).AutoFilter
End Sub

[marshybid]

Im looking to delete all rows on my spreadsheet that do not have a particular value in column D. I have no code so far by i suspect i need some kind of for loop?

any help?

Regards

PsYiOn

Hi PsYiOn,

I'm sure that oters will be able to give you a better option, but I use the following to do exactly what you are trying to do;



Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long

Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 '// this counts from the last row up to row 2 - misses out headers
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then '// This checks that column A has a value - counting from last row up until finds first instance of value in column A

'// Delete data that we do not need
If myBaseRow.Cells.Item(1, 4) <> "your criteria" Then


myBaseRow.Delete

End If
End If
Next

End Sub


I hope that this helps you.

Marshybid :yes

[marshybid]

Hi, I must have been writing my response as xld was posting his.....

Given the number of queries that xld answers for me I would always defer to xld's far superior knowledge.

I would be interested in what xld thinks of the method I use though :thumb

Thanks,

Marshybid

[Bob Phillips]

There is nothing wrong with your solution, I offer a similar solution to this type of problem myself very often.

The filter method is just streets ahead of the loop when teh data gets to any size (hundreds of rows). This is one of those examples that I advocate, tapping into Excel's builtin functionality.

[marshybid]

There is nothing wrong with your solution, I offer a similar solution to this type of problem myself very often.

The filter method is just streets ahead of the loop when teh data gets to any size (hundreds of rows). This is one of those examples that I advocate, tapping into Excel's builtin functionality.

Thanks Bob,

So where I am using my method for worksheets with 18,000 rows of data I should probably consider replacing it with the filter method that you suggested. :yes Might just help to speed things up a bit!!!!

Why is a test column inserted?? Could I not just filter the existing data column then entirerow.delete??

Marshybid

[Bob Phillips]

So where I am using my method for worksheets with 18,000 rows of data I should probably consider replacing it with the filter method that you suggested. :yes Might just help to speed things up a bit!!!!


Definitely!


Why is a test column inserted?? Could I not just filter the existing data column then entirerow.delete??

That was a hangover from my code archive, where I create a column to put a criteria formula to determine which rows to delete. I have removed it now.

[marshybid]

Hi Bob,

That is great.

I'm going to give it a go on my code right now. Should save me about an hour or two!!! :clap: 18,000 rows by 50 columns of data was taking an eternity to run... And now I know why.

How do you edit a previous response?? You responded with original code and have then updated the same response, removing the test column component.... Very clever.

Marshybid

[Bob Phillips]

How do you edit a previous response?? You responded with original code and have then updated the same response, removing the test column component.... Very clever.

When the post owner views a post, as well as a quote button there is an edit button.

Make sure you grab the code as it is now.

[Paul_Hossler]

FWIW -- my experience is that if there are a lot of rows to be deleted scattered all over the WS, it's significantly faster to

1. create a temporary "Original Order" column with the orig row num,
2. mark the rows some how (I usually set the value to TRUE),
3. sort by that column to group all the TRUEs
4. delete what is now a single large chunk of rows
5. Re-sort using the Original Order
6. Delete the temp column

Seems that deleting one row at a time requires just as much "re-packing" time as deleting a single group, and having to delete 1000 single rows in a For loop seems to take a lot of time, even after the 2 Sorts

Paul

[marshybid]

When the post owner views a post, as well as a quote button there is an edit button.

Make sure you grab the code as it is now.

Am grabbing it now.

Another quick query on this...

In my code some rows are removed based on value in Column D (4) others based on value in Column B (2)

I assume I just need to do the following



Const TestColumn As Long = 4
Const TestColumn2 As Long = 2


Is this correct??

Also, I have still more rows that are deleted based on multiple criteria (value of 2/3 cell references), how would I implement that into your code??

For mine I would just do the following;



Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 '// this counts from the last row up to row 2 - misses out headers
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then '// This checks that column A has a value - counting from last row up until finds first instance of value in column A

'// Delete data that we do not need
If myBaseRow.Cells.Item(1, 4) <> "criteria 1" And myBaseRow.Cells.Item(1, 6) <> "criteria 2" Then


myBaseRow.Delete

End If
End If
Next


Thanks,

Marshybid:doh:

[Bob Phillips]

We are not suggesting a For loop.

So how do you identify which get marked as TRUE? A For loop?

[PsYiOn]

i have been trying this code

Sub DeleteRCAyes()

Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long
Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 '// this counts from the last row up to row 2 - misses out headers
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then '// This checks that column A has a value - counting from last row up until finds first instance of value in column A
'// Delete data that we do not need
If myBaseRow.Cells.Item(1, 12) <> "Yes" Then
myBaseRow.Delete
End If
End If
Next
End Sub




It seems to be deleting everything, i need to check for the value Yes in column M and delete all rows containing yes


Where in thsi code am i idenifying what column to check?

[marshybid]

Hi PsYiOn,

You need to modify the code as below;



Sub DeleteRCAyes()
Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long
Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 '// this counts from the last row up to row 2 - misses out headers
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then '// This checks that column A has a value - counting from last row up until finds first instance of value in column A
'// Delete data that we do not need
If myBaseRow.Cells.Item(1, 13) <> "Yes" Then
myBaseRow.Delete
End If
End If
Next
End Sub


Copy and paste the code above in.

The only thing I have changed is the following line

If mybaseRow.Cells.Item(1, 13) <> "Yes" Then

This reads as: If mybaseRow.Cells.Item(row 1, column 13(M)) <> "Yes" Then

Try it now, it should work fine, assuming you always have a value in all cells (rows) that you are searching through in column 1(A)

This is managed by the following code;




If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then



The macro will start at the last row in your worksheet whether blank or not and work up until it finds the first cell in column 1(A) with data, then it will look at each row moving up one at a time and if it does not find "Yes" in column 13(M) it will delete the entire row. This will repeat until either column 1(A) is blank or until row 2, this will leave your headers in place.

Hope this helps.

Marshybid :hi:

[Ago]

Xld i got your way to work when i use a blank sheet.
but when i try to implement your way in my sheet it wont work.

i have a sheet with data in column B-M.
my plan was to use this way that you posted in my Duplicates macro.
it basicly searches for duplicates and removes the line.

i dont have "some value" that i can remove so first i have to run a for loop to find the duplicates, when one is found it writes "delete" in column A.

after that i run your script and it stops at the marked line.

Public Sub DeleteRowsUsingAutofilter()
Const TestColumn As Long = 1
Dim cRows As Long
'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
'finally, apply the autofilter for al matching cells
Columns(TestColumn).AutoFilter Field:=1, Criteria1:="Delete", Operator:=xlAnd
'we now have only matching rows visible, so we can
'delete these matching rows
With Cells(2, TestColumn).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Columns(TestColumn).AutoFilter
End Sub



i think this is because i have another filter in the same sheet (column B-M).
is there a way that i can remove my filter (B-M), delete the lines and then place a filter at B-M again?

[marshybid]

Hi Ago,

if I want to remove filters on a worksheet prior to running any macro I use the following line of code;



ActiveSheet.AutoFilterMode = False


So you could use this in xld's code as follows, added line shown in orange;

[code]
Public Sub DeleteRowsUsingAutofilter()
Const TestColumn As Long = 1
Dim cRows As Long
ActiveSheet.AutoFilterMode = False
'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
'finally, apply the autofilter for al matching cells
Columns(TestColumn).Autofilter Field:=1, Criteria1:="Delete", Operator:=xlAnd
'we now have only matching rows visible, so we can
'delete these matching rows
With Cells(2, TestColumn).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Columns(TestColumn).Autofilter
End Sub


This works fine for me, I tested it today in a dummy worksheet.

Marshybid :rotlaugh:

[Ago]

didnt work for me:(

i posted a sample with the current code im using.
i have commented the code that is not "usefull" at this moment.

i get a range error, dont know why.

[Bob Phillips]

What is your point mate? You seem to taking a snipe here, but I cannot see the rhyme or the reason.

Do you want to know how to quickly duplicate rows quickly, or are you just trying to tear down other efforts?

[marshybid]

Hi xld,

As per my previous response, the addition of the line


ActiveSheet.AutoFilterMode = False

Worked fine for me.

On another note, I have added the code into my macro, however the following does not work for me



Const TestColumn4 As Long = 47
Dim cRows As Long

Sheets("Sheet name here").Select
On Error Resume Next
'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn4).End(xlUp).Row
'finally, apply the autofilter for al matching cells
Columns(TestColumn4).AutoFilter Field:=1, Criteria1:="A", _
Criteria2:="B", Criteria3:="C", _
Criteria4:="D", Operator:=xlOr
'we now have only matching rows visible, so we can
'delete these matching rows
With Cells(2, TestColumn4).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Columns(TestColumn4).AutoFilter
On Error GoTo 0


Is it because I have too many creiteria??? It works fine with 1 or 2 criteria, but simply deletes all rows when I have 4 criteria :dunno

Thanks, Marshybid

[Ago]

What is your point mate? You seem to taking a snipe here, but I cannot see the rhyme or the reason.

Do you want to know how to quickly duplicate rows quickly, or are you just trying to tear down other efforts?


sorry i dont understand.

but no, i do not want to duplicate rows. i want to delete the duplicated lines.

if you look at the sample i uploaded and run the duplicates2 macro it will put "delete" in column A at those lines that is supposed to be deleted.
but when i use the DeleteRowsUsingAutofilter it does not delete these lines.
what am i doing wrong?

[marshybid]

sorry i dont understand.

but no, i do not want to duplicate rows. i want to delete the duplicated lines.

if you look at the sample i uploaded and run the duplicates2 macro it will put "delete" in column A at those lines that is supposed to be deleted.
but when i use the DeleteRowsUsingAutofilter it does not delete these lines.
what am i doing wrong?

Don't know if this would be affecting the macro for you but you have the following line in your code


With Cells(3, TestColumn).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Cells(3,.... refers to Row 3, your data seems to start from Row 6, maybe try Cells(6,......

Row 3 in your data is blank and contains no data

Marshybid

[Bob Phillips]

I actually meant delete duplicates quickly ...

It doesn't delete duplicates because it wasn't designed to delete duplicates, it was designed to delete a given value. To delet duplicates use


Public Sub DeleteDuplicateRowsUsingAutofilter()

Const TestColumn As Long = 5 '<== change if wrong
Dim cRows As Long
'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
'now add a column for our test formula
Columns(TestColumn + 1).Insert
With Cells(1, TestColumn + 1)
'add formula in first row to test uniqueness
.Formula = "=COUNTIF(" & Cells(1, TestColumn).Address & ":" & _
Cells(1, TestColumn).Address(False, False) & "," & Cells(1, TestColumn).Address(False, False) & ")>1"
'copy down to all rows
.AutoFill Destination:=.Resize(cRows)
'now add a header row for autofilter
.EntireRow.Insert
End With
'finally, apply the autofilter for al matching cells
Columns(TestColumn + 1).AutoFilter Field:=1, Criteria1:="TRUE", Operator:=xlAnd
'we now have only matching rows visible, so we can
'delete these matching rows
With Range(Cells(1, TestColumn + 1), Cells(cRows + 1, TestColumn))
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
'tidy up by deleting the column we added
Columns(TestColumn + 1).Delete
End Sub

[Bob Phillips]

Hi xld,

As per my previous response, the addition of the line


ActiveSheet.AutoFilterMode = False

Worked fine for me.

On another note, I have added the code into my macro, however the following does not work for me



Const TestColumn4 As Long = 47
Dim cRows As Long

Sheets("Sheet name here").Select
On Error Resume Next
'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn4).End(xlUp).Row

'finally, apply the autofilter for al matching cells
Columns(TestColumn4).AutoFilter Field:=1, Criteria1:="A", _
Criteria2:="B", Criteria3:="C", _
Criteria4:="D", Operator:=xlOr

'we now have only matching rows visible, so we can
'delete these matching rows
With Cells(2, TestColumn4).Resize(cRows - 1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Columns(TestColumn4).AutoFilter
On Error GoTo 0


Is it because I have too many creiteria??? It works fine with 1 or 2 criteria, but simply deletes all rows when I have 4 criteria :dunno

Thanks, Marshybid

Yes it is, there are only 2 criteria in autofilter.

The way to do it is to create a helper column with a multi-condition test formula that returns TRUE or FALSE, and filter by that helper column. See my example in the duplicates delete above, it is only a one condition test, but it has a criteria that autofilter cannot replicate.

[PsYiOn]

Hi PsYiOn,

You need to modify the code as below;



Sub DeleteRCAyes()
Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long

Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 '// this counts from the last row up to row 2 - misses out headers
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then '// This checks that column A has a value - counting from last row up until finds first instance of value in column A

'// Delete data that we do not need
If myBaseRow.Cells.Item(1, 13) <> "Yes" Then


myBaseRow.Delete

End If
End If
Next

End Sub


Copy and paste the code above in.

The only thing I have changed is the following line

If mybaseRow.Cells.Item(1, 13) <> "Yes" Then

This reads as: If mybaseRow.Cells.Item(row 1, column 13(M)) <> "Yes" Then

Try it now, it should work fine, assuming you always have a value in all cells (rows) that you are searching through in column 1(A)

This is managed by the following code;




If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then



The macro will start at the last row in your worksheet whether blank or not and work up until it finds the first cell in column 1(A) with data, then it will look at each row moving up one at a time and if it does not find "Yes" in column 13(M) it will delete the entire row. This will repeat until either column 1(A) is blank or until row 2, this will leave your headers in place.

Hope this helps.

Marshybid :hi:
Works a treat buddy thanks alot you really helped me out. :)

[PsYiOn]

I seem to be having a problem.


Sub SortTodayUser()
Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long
Set myBaseWorkSheet = ActiveWorkbook.ActiveSheet
Set myBaseRange = myBaseWorkSheet.Rows
For RowsCounter = myBaseRange.Rows.Count To 2 Step -1 '// this counts from the last row up to row 2 - misses out headers
Set myBaseRow = myBaseRange.Item(RowsCounter)
If Len(myBaseRow.Cells.Item(1, 1)) <> 0 Then '// This checks that column A has a value - counting from last row up until finds first instance of value in column A
'// Delete data that we do not need
If myBaseRow.Cells(1, 9) <> Date Then
myBaseRow.Delete
End If
End If
Next
End Sub

This is giving me issues, the same sample of code worked in a different part of my program but now I want to filter by a different column "I" this just seems to delete everything. Is this because i am using the Date function?

any ideas?