what does the count function do and .end

[tlinton]

Hi all,

I ran into a spreadsheet that has a VBA code I don't undestand. From "LR =" to "select case right" is the part I don't understand. Can someone please explain what is happening with the rows.count end xlup part? Thanks!

Please Login or Register  to view this content.

[jeffreybrown]

I added comments to the code, but maybe this reference will also help

Please Login or Register  to view this content.

[tlinton]

Thanks so much Jeff. One last question for you. I'd like to take those rows and paste them into a new worksheet, but I dont know how to create a nested loop. I'm assuming I have to make another loop because excel wont know to move to the next row to paste the contents there instead of constantly pasting rows in say a1 of worksheet titled "breakdowns". Any ideas?

[jeffreybrown]

I believe the most efficient method would be to have a formula in the last column of your data and match against the values you want to keep. (those value are on Sheet3 in a named range) If there is a match, the formula will return a number, and if not, then #N/A.

No run a filter on the new column and transfer all those rows to the new sheet.

If you want to delete the rows altogether then just uncomment the line in green. (remove the ')

[tlinton]

Thanks for all your help Jeff!! I'll look at your last post tomorrow when i get in. i need to try to understand what you have going on!