Use Query To Get Specific Rows

[lbird2]

I need to extract specific rows from all the spreadsheets in a directory.

I can use the Query editor to load all of the workbooks but the there isn't a quick choice to select certain rows. For example, I would like to keep rows 4, 53, 57, 71 and 57 from all of the loaded workbooks.

I suspect I have to use the advanced editor but I don't know the proper code to use.

Any help would be greatly appreciated.

 

[lbird2]

Now that I think of it, maybe VBA is the better way to go.

 

[lbird2]

*Bump*

 

[alansidman]

When you use the term "extract," what does this specifically mean?
1. I could interpret that you wish to delete all the other rows in the file.
2. You wish to cut those rows from the sheet and paste them elsewhere. If so, where/
3. You wish to copy those rows and paste them elsewhere. If so, where?

Help us to help you with complete instructions on your wishes. We cannot see your screen and do not have mind reading capabilities.

 

[lbird2]

Thank you for your response Alan, and I apologize for not being clear.

As I said, I would like to cycle through all the files in a folder, and copy those particular rows to another separate workbook (say "data"). The end result would be a consolidated workbook containing all of those rows.

I hope that makes more sense.

 

[BlueHornet]

Again, you're not really giving enough information to complete the task. There's no indication of how those rows should be copied ... to a single worksheet? to separate worksheets for each worksheet or workbook that they're taken from? preserving the identity of the source as to workbook and worksheet? (And if so, how?) From each worksheet in each workbook? How will merged cells and objects be handled, if any exist? Do you only want "contents" (i.e. "values") or do you want to preserve formulas? Finally, though it's unlikely that you have so many workbooks in a directory or folder to overwhelm the capability of one worksheet to maintain the contents of 5 rows per workbook in the directory ... it's not impossible, either.

 

[alansidman]

Please re-read my post #4 and answer the questions specifically. We cannot help you with generic information. You need to be specific if you expect a viable solution from anyone on this forum.