Loop through rows and delete based on criteria

[yolandaro30]

Hello, I'm a novice user of vba and need assistance creating a macro to loop through rows of data and delete rows based on criteria in column C. This is a weekly report and on average has about 10k- 12k rows of data. There are centers/locations in column C (i.e. Charlotte, Chicago 1, Tulsa 5, etc). I want the macro to keep only the first 10 rows of data for each location and any other rows for that location needs to be deleted then move on to the next location and do the same, etc. If the location has less than 10 rows, then those rows will stay as is and not be deleted. Any assistance with this would be greatly appreciated.

[benishiryo]

hi yolandraro30, welcome to the forum. try this:

Please Login or Register  to view this content.

[yolandaro30]

Thank u so much. This worked perfectly!!!

[yolandaro30]

As I mentioned i'm a novice, so I'm still trying to understand. I understand the first for loop, but can you breakdown the second one for me. especially the part with "2 Step - 1". Thanks again for taking your time to help with this.

[stnkynts]

A loop is designed to run the code within it a certain number of times. Range("A" & Rows.Count).End(xlUp).Row is the last used row in column A (lets say it is 100). So this loop is designed to run 100 times. But if you put 100 first, the code is going to think that after doing the first operation, you are already at 100 so it should be done. That is where the Step -1 comes into play. By saying 100 to 2 step -1 you are telling it to go backwards, 100, 99, 98, etc all the way down to 2.

With that in mind, "Cells" is a range just like when you see "Range" but it uses a numerical index. So Cells(1,1) is actually "A1". With that bit of information you can look at the code as checking each row in column A from 100 to 2 (in this example) to see if its value is greater than 10. If it is, delete that row.

A side note. The reason benishiryo went backwards in the loop is because if you try to delete going forward (ie 2 to 100) it gets screwed up.

I hope I explained this in a way that you can understand and which answers your question.

[yolandaro30]

Thank u. That was very well explained. As I move forward with trying to write more macros understandng little things like that really helps a lot. Even with all the Google-ing and use of an excel programming book I haven't really come across any info that breaks down and explain certain things the way you just did. So thanks a lot. I really appreciate it.