Help with sorting data and copy/pasting a range instead of a loop check for each row

Hello,
I'm very new to VBA and trying to streamline the current macro I've been using, but the answer is beyond my current knowledge of the language and I haven't had much luck searching.

I am currently using a counter setup to check a value of a particular column in my data which is either 'SALE' or 'REMOVE', then copying a part of the row if it finds 'SALE' (pasting it to another worksheet), or skips to the next row if it finds 'REMOVE'.

Its doing this several thousand times and I feel the code could be written much better to sort all the data so all of the rows with 'SALE' line up, and I can copy/paste a range instead of doing it row by row.

Is there a command similar to .Cells(Rows.Count, "A").End(xlUp).Row, but instead of returning the last row number in column A with a value, it returns the last row number in column A with the value of 'SALE'?

Any help would be greatly appreciated!

Here is the current code I've been using. It works, but just takes far too long and feels clunky.

Please Login or Register  to view this content.

Thank you!

Thanks for the tips, here's where I'm at.

This is the current Macro I'm using to sort the information. It goes down a list of unsorted data (A:B in attached worksheet) and line by line checks the status of column B and copies that rows column A into the appropriate workbook:

Please Login or Register  to view this content.

The problem is that there are several thousand lines for this to do, and it ends up taking much longer than I think could be possible.

What I was hoping to do is change the macro to sort the information first (G:H), then find the last row with the value 'SALE', and copy a range and paste that into the proper worksheet.

Where I'm stuck is finding a command similar to '.Cells(Rows.Count, "A").End(xlUp).Row', but instead of returning the row number of the last filled cell it returns the row number of the last cell with a particular value.

Here is workbook with a sample of data.
ForumQuestion.xls

Hope this helps clarify, thanks!

HI,

Objective: I am also having similar need and have a data sheet (refer sheet names as cost). In this sheet there are some cost element and i want these cost elements on the different sheet to prepare an analysis on cost element wise. I tried to change some VBA codes i saw on this forum but still facing the problem. Data is coming but it is again and again getting pasted on the destination sheet. Sheet is attached for your ready reference.