Delete Rows Based On Criteria Excel VBA Macro

Excel macro/VBA code available in this post: excelmacroclass.blogspot.com/...
In this video we see how delete rows upon certain criteria in Excel with VBA macros. The macro loops through each row with content in the worksheet, and checks if certain criteria for one or more fields is met to delete the row. The criteria can be any value along with the relational operators of the If statement. For example, that could be when the value is below a certain price, or when the profit is above a certain margin. It can also check for specific text, a string of certain length, etc.
You can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: excelmacroclass.blogspot.com
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)

[@Chef-1707]

This is great and thanks for sharing.
Well laid out and explained in a way that someone like myself who only has basic knowledge of VBA can begin to understand. I really appreciate.

[@fatymcgordis4367]

This was of great help! thanks you for sharing!

[@alexiki]

This is really good info, mate.

[@ginolatino7081]

Thx for sharing!
Good job!

[@shawonshah]

Huge time saving code
Just loved it❤

[@terrykahn]

Thanks a lot for your efforts my friend. subscribed. greetings from İstanbul.👏🙏✌

[@jdgomos]

Thanks a lot, it works perfectly 💪

[@KatiesBookNook426]

this is so helpful!! how would I do this if I wanted to get rid of every row that doesn't have a defined list of values in the row (i.e. i only want to keep the rows where the numbers match my defined set of values)

[@ExcelMacroMania]

Where are the defined values? I guess in a range or even single cell .. let's assume you can put them in an array.
Dim defValues As Variant
defValues = Array("1", "10", "100")
Inside the rows loop you need to add another loop to check each defined value in defValues against a given cell in that row (or several cells). Let's start simple and consider only 1 cell or 1 column ,column A for example. I also assume we are talking about numerical values, but could be done for text too.
For dataRow = lastRow To 2 Step -1
targetCell = Range("A" & dataRow).Value
For Each defValue In defValues
If Val(targetCell) = Val(defValue) Then
Rows(dataRow).Delete
Exit For
End If
Next defValue
Next dataRow

[@KatiesBookNook426]

@@ExcelMacroMania thank you! this worked except it deleted all of the values I wanted to keep. I instead want to delete all the values that are NOT my defined set. The context is I have a dataset I pulled off of a lab instrument with all of the samples run in a particular date range, with each row have a sample number. I want to keep only the sample numbers that match my experiment and delete the rest. I tried subbing out the = with a (not equal) but it just deleted my entire sheet. Any advice would be appreciated!!

[@ExcelMacroMania]

@@KatiesBookNook426 Here's a way to do that, make sure you set the Boolean to False before the array For Each loop, and you have the condition to check that Boolean after the For Each Loop. The Boolean is "ValueFound" is just a variable that can be True or False. Replace that inside the dataRow loop.
ValueFound = False
For Each defValue In defValues
If Val(targetCell) = Val(defValue) Then
ValueFound = True
Exit For
End If
Next defValue
If ValueFound = False Then Rows(dataRow).Delete

[@MrDanSmith]

Thanks for the video. I'm new to VBA and this helped but this is giving me a mismatch error 13. Used the same code except I have 3 conditions: numSet (usually a one-digit number), numDone (usually a one-digit number), and numRemains(usually a one-digit number), where I said if numSet>0 AND numDone>0 AND numRemains = 0....The code does its job and deletes the rows I want deleted, but when it finishes I keep getting this mismatch error 13. I tried changing the variables to long but still the same error. Anyways, can you help?

[@ExcelMacroMania]

Type mismatch (error 13) is a very common error, it can be due to many things... But if you didn't change anything, only those variables... maybe you are looping forward instead of backwards? Backwards means the loop goes from the last row with content to the 1st or 2nd row, using Step -1. Could it be you forgot that? See the code in the post: excelmacroclass.blogspot.com/2022/10/delete-rows-based-on-criteria-excel-vba.html

[@MrDanSmith]

@@ExcelMacroMania I have: Sub DeleteOld_Click()
Dim lastRow As Long, dataRow As Long
Dim numSet As Integer, numDone As Integer, numRemains As Integer

lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For dataRow = lastRow To 2 Step -1
numSet = Range("G" & dataRow).Value
numDone = Range("H" & dataRow).Value
numRemains = Range("I" & dataRow).Value

If numSet > 0 And numDone > 0 And numRemains = 0 Then

Rows(dataRow).Delete

End If

Next dataRow

End Sub
Not sure if it makes a difference that my table starts on D4 (D5 being the first data cell). Like I said, the code does its job, it just gives me Error 13 when it's completed, almost as if it's looking for more cells to delete? Could this error happen if there is no data in the table once the deletions are performed?

[@MrDanSmith]

@@ExcelMacroMania Side note. Debug automatically selects numSet = Range("G" & dataRow).Value

[@ExcelMacroMania]

@@MrDanSmith Hmm, not sure then why. But maybe declare numSet As Variant, for example. Maybe is dataRow that needs to be declared as Long if you have many rows. Otherwise, you can always use error handling, On Error Resume Next will ignore the error (if it works after all), or On Error GoTo ... can go to another place in the code to handle the error. See more about error handling and debugging in this other video: ua-cam.com/video/Rg-gXg8MYKc/v-deo.htmlsi=aKysKvBQOwJTMoSs

[@bablooabhay]

Can excel macro filter data based on input count. For example out of a 1000 rows data i need only 20 rows for each name match. Whenever i input the count and name in different sheet it should give me data of those rows. Is that possible in excel

[@ExcelMacroMania]

Yes, sure. See the basics of filtering with VBA in this other video: ua-cam.com/video/In03DKEz5sQ/v-deo.htmlsi=GPUHu8pisZ9WlTa_
And find a practical example to filter data with VBA here: ua-cam.com/video/Do84-RRoLbE/v-deo.htmlsi=eKUO510Nj9Juyxe8

[@bablooabhay]

Thank you so much that helps. I have seen both the videos and it answered one part of my question. The other part is i need only 10 (this count can change based on requirement) rows of data randomly with the same name criteria and not every that matches. Please help me on this. I have came through till filtering of data but out of the same names i need only 10 row to be picked not everything. Kind of a sampling. Please help.

[@ExcelMacroMania]

@@bablooabhay If you want to get the filtered rows within the first 10 rows you would use the following:
Range("A1:A10").SpecialCells(xlCellTypeVisible).EntireRow.Select
Instead of Select, you can use Copy, and then paste it in other sheet for example.
But if you want to get the first 10 filtered records, which could correspond for example to rows 2,3,6,9,12,15,18,21,... then you need to loop through each filtered row (also with SpecialCells(xlCellTypeVisible) as above, and then use a counter to do it 10 times and put it into an array, or copy to other sheet, etc. You could do something like this:
For Each rrow In Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows
n = n + 1
If n > 5 Then Exit For
rng = rng & rrow.Address & ","
Next rrow
rng = Left(rng, Len(rng) - 1)
Range(rng).Select

[@bablooabhay]

Thank you so much for your help!!!