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
5 ай бұрын
This was of great help! thanks you for sharing!
@terrykahn
Жыл бұрын
Thanks a lot for your efforts my friend. subscribed. greetings from İstanbul.👏🙏✌
@shawonshah
8 ай бұрын
Huge time saving code Just loved it❤
@jdgomos
11 ай бұрын
Thanks a lot, it works perfectly 💪
@ginolatino7081
11 ай бұрын
Thx for sharing! Good job!
@alexiki
Жыл бұрын
This is really good info, mate.
@KatiesBookNook426
10 ай бұрын
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
10 ай бұрын
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
10 ай бұрын
@@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
9 ай бұрын
@@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
@bablooabhay
6 ай бұрын
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
6 ай бұрын
Yes, sure. See the basics of filtering with VBA in this other video: kzitem.info/news/bejne/qqRmmXiBfqxmpoYsi=GPUHu8pisZ9WlTa_ And find a practical example to filter data with VBA here: kzitem.info/news/bejne/paVummGIi6F9lXosi=eKUO510Nj9Juyxe8
@bablooabhay
6 ай бұрын
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
6 ай бұрын
@@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
6 ай бұрын
Thank you so much for your help!!!
@MrDanSmith
11 ай бұрын
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
11 ай бұрын
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
11 ай бұрын
@@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 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: kzitem.info/news/bejne/s51jzYydcX-Kfpgsi=aKysKvBQOwJTMoSs
Пікірлер: 21