extract rows based on value in column A

hi guys,

my limited knowledge is letting me down.

I have a worksheet with around 140000 rows. for each time any of the 4 values below appear on the worksheet, i need that row copied and transposed to a new worksheet we'll call "summary".

Ideally it would result in a table with 4 columns on that worksheet headed with the values below.

The values are; PRODUCT NUMBER, PRODUCT DESCRIPTION, PRODUCT TYPE and PRODUCT SELECTION.

I've attached an example with just over 1000 rows, it carries on in the same fashion for 140000 rows.

Thanks for any help you can offer guys.

Hi Sixthsense,

i've attached a file with an example of the output i need. hope this helps?

try this code

Please Login or Register  to view this content.

ya its because of the blank only and if so please calculate the lastrow based upon the column which has the highest content in it

let me know whether there is any condition like if product no is empty then its related details should not be copied??

Hi Punar,

last row with data is 140737 and last column is IJ.

if row blank then resume next?

Hi AB33,

thanks for your response. However, it debugs at the line of code below, errors message stating "Method 'PasteSpecial' of object 'Range' failed", i hit debug, close, then all of my menu options are greyed out!

Please Login or Register  to view this content.

Please see attached. It does not show any error