VBA to filter data and cut entire row and paste on the same sheet last blank row

[lday75]

I need to know how in VBA to cut rows out of the sheet if cells in column G contain the word "Constellation" and paste those rows to the last blank line in the sheet. I have tried a number of different ways and keep getting poor results, one being the fact that when it pastes I get all the blank rows in between where the data was cut from. I do need this in VBA and cannot get it work at all.

Here is code that I thought would do the trick, but it seems to do nothing at all.

Please Login or Register  to view this content.

Any thoughts?

[arlu1201]

Do you have a sample file that you can upload?

[lday75]

Yes, I have blocked out customer info, so you will see a lot of **** but this is what I have been working on, Thank you

[lday75]

Ok, so I found a way to get this accomplished, however I do not think it is the most efficient way to get this done. I have pasted my code below, does anyone know of a better way to accomplish this?

Please Login or Register  to view this content.

[arlu1201]

Try this code

Please Login or Register  to view this content.

[lday75]

Arlette,

I put this code in, however it does not seem to do anything. I run it, and the screen flashes for a split second then ends. Nothing gets copied to last. Sorry, but thank you anyways

[arlu1201]

Column A was blank hence it didnt run. I have edited it (though i dont understand the reason behind copying the row again at the last blank row).

Please Login or Register  to view this content.

[lday75]

Due to the fact that most of the records on this sample set report are for the Constellation Groups, my team lead wants to have all of those records at the end of the file. Out of the 694 Records, 567 of them are for the Constellation groups, therefore my lead wants them at the end. You code did work this time, with only one little quirk, all the records that got moved, and now in "inverted" order! LoL. Which I do apologize, I had the same issue when I tried it in a similar way that you did. For each client group, or "household" there is a (M)aster record, and then a bunch of (S)ubordinate records. This would be the values in col B, either U - unique, M - master or S - subordinate. When setting the Const groups to the end using the code you provided since this moves one row at a time, the Master record ends up below the subordinate records that belong to that master. Again, I do apologize, as I had come across this same issue with previous iterations of this, however I did NOT remember to add that in my initial post on this thread. Again, thank you for the help. I am sorta new to VBA, well have been coding in it for about 2 years now, but have not had any formal training or schooling on it, so most of my knowledge is from forums and peeps such as yourself, so THANK YOU, very much, I do appreciate all the info I can get.

[arlu1201]

Ok, so how do you want the data to be moved? In groups ?

[lday75]

Well in this case, prior to the move, rows 85 - 651 all are for Const groups. I would cut them all, and put those to the end of the sheet so that they end up in rows 129 - 695. in the same order they were.

[arlu1201]

But what if your data changes, the row numbers wouldnt remain the same right? So the macro has to work based on some logic.

[lday75]

exactly, and hence the migraine I have had with this little part!! so, I decided to go the ling way around, which works fine, but just was not sure if this was the most efficient way of doing this. I end up filtering, cutting the rows, pasting to new sheet, then deleting the cut rows from HH_Samp sheet, then pasting the rows back into HH_Samp at the end, then deleting the sheet that was created... it just seems like going around my **** to get to my elbow, but it works. And with only 695 records, does not take a while to do. Now, if the data set become larger, 65K rows, etc, then I may have to find a different solution.

[arlu1201]

Maybe you can specify some logic on how i can group the rows and then copy them down. If there is any field that needs to be compared to the row below it and they can be grouped together and then copied / moved?

If there is nothing, then i can work with your logic.

[lday75]

sorry there isn't anything else to relate which subordinate records go with which master, unless there would be another column added, which my lead does not want. I had asked to be able add a identity type column and he did not like that idea, so I just went with what I had. sorry

[arlu1201]

Ok, then i will try the way you do.

[lday75]

Thank you, and again, please do not feel obligated by any means to dig very deep into this. At this point it is working, I was just seeing if there was a more efficient approach that I may not be aware of that could get this done.