select all rows with data in column j

[JChandler22]

I have a spreadsheet with thousands of rows. In column J, there is data in some rows, but not all.

What would be the best way of selecting every (entire) row that DOES have data in row J? I would like them all selected at the same time to move them to a different spreadsheet.

I'm guessing a macro would be the easiest way, but I'm not too sure where to start. Some sort of loop function?

[antoka05]

Try with this macro:

Please Login or Register  to view this content.

Regards,
Antonio

[JChandler22]

Thanks Antonio... almost there, but not quite what I'm looking for.

Your method simply cuts and pastes the entire J row into a new sheet.

What I need is... for every row that has data in column J, I need to copy that ENTIRE row, and then paste all of those rows into a new sheet (with no blank rows in between).

Any other suggestions? Thanks!

[KA_Analyst]

I've just had to do this and wrote some code - it works for me

Please Login or Register  to view this content.

Apologies if it's not the right thing

[antoka05]

Try with this macro:

Please Login or Register  to view this content.

Regards,
Antonio

[Tristan]

Hi JChandler,

Macros will do what you're after but you might not need them at all. Try the following and it could achieve what you want really easily:

• From the worksheet go to Data -> Filter and select "AutoFilter"
• Each of the header columns will now have a pull-down menu from where you select the criteria for the filtering
• From the pull-down menu in Column J select "NonBlanks"
• This should now just show those records (rows) that have some data in column J.
• Simply copy and paste to wherever.

Hope this helps

Tris