VBA Grouping Concatenate best way to handle, based on data in column

Hi, when you want to loop through rows and delete rows at the same time you need to do it slightly differently because deleting a row will cause your loop to be off by a row every time you delete one.

you can follow it and adjust with a variable using a for loop but i like to just a while loop because its easier to follow.
obviously using your current method of getting the last row as a marker to the end of the table we will keep count of the deleted rows to offset this value

Please Login or Register  to view this content.

just add the code here to your own, and comment out your entire loop or delete it....

i didnt add the progress bar code here

remove the do events if you like, i always keep it in there while testing....... and even after just do something like 'If Rw MOD 1000 = 0 then doevents' so you can at least get a change to recover from an infinate loop................ but this loop should be fine....

Actually your way to loop through the range is correct and does not need Change when you start from the Bottom you do not screw up the process by deleteing a row.
Your code is perfectly fine you just delete the wrong row :D



it needs to be

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Edit: Also i wanna mention that the initial code with the for loop performs around 33% better than the solution with the while loop. with a small data set that is not really noticable but in a dataset with 50k rows for example the for loop is running through in 2 minutes and the while loop needs 3 minutes which is a big difference.

You could speed up the process even more by reading everything into an array loop through that and only write back the values that you need. but unless you have a massive amount of Data that would not make much noticable difference.

Hey, your right haha , but i like to move forward