Programming a Macro to select row ranges based on a formula

Hi everyone,

I feel like this is a rather newbie question, but my googling efforts haven't unearthed an answer. I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet.

There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document.

If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc...

Here's the code I have developed thus far:

Please Login or Register  to view this content.

workaholic,

Welcome to the Excel Forum.

Please post your workbook - scroll down and see "Manage Attachments".

workaholic,

See the attached workbook "Do Loop SR ER NR RNG - move data - transpose - Raw DataSample(1) - workaholic - SDG.xls" with macro "MoveData".

Run the "MoveData" macro.

I added/changed all your "Data #" with formulas to test the macro, on sheet "Raw Data".

The macro will first clear the cell contents on sheet "Output" from range A2:D_last_row.

Then copy, and then paste special and transpose onto sheet "Output"