Extracting a different amount of rows from large amounts of data

[larzukmoose]

Hello!

I could use some help extracting data from one sheet and moving it to another where it will be easier for me to analyze. The data is text information that is all in one column and looks something similar to this:

Start Word
Data
Data
Data
End Word
Nonsense
Nonsense
Nonsense
Start Word
Data
Data
End Word
Nonsense

I would like to be able to have a macro that I could run that would, on a new sheet, give me an output that would look like:

Start Word
Data
Data
Data

Start Word
Data
Data

And so on, going through the entire column and clearing out all of the nonsense and end words that I don't need to look at. I had a macro that I found on a different site that was able to find the start word and copy any number of rows down and move them to a different sheet. The issue is that I had set that number of rows to 5 or something, and sometimes the data is longer than 5 rows and sometimes it is less. Is there a way to set it to stop once it reaches an end word?

Thanks so much in advance for any help!

 

[QU4487]

This might not be what you're looking for, but as nobody else how responded yet, it might be better than nowt.

Excel 2012

	A	B
Start Word	Start Word
Data	Data
Data	Data
Data	Data
End Word
Nonsense
Nonsense
Nonsense
Start Word	Start Word
Data	Data
Data	Data
End Word
Nonsense

<tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(A2="Start Word",A2,IF(OR(A2="End Word",ISNUMBER(B1)),0,A2))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(A3="Start Word",A3,IF(OR(A3="End Word",ISNUMBER(B2)),0,A3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(A4="Start Word",A4,IF(OR(A4="End Word",ISNUMBER(B3)),0,A4))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=IF(A5="Start Word",A5,IF(OR(A5="End Word",ISNUMBER(B4)),0,A5))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=IF(A6="Start Word",A6,IF(OR(A6="End Word",ISNUMBER(B5)),0,A6))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=IF(A7="Start Word",A7,IF(OR(A7="End Word",ISNUMBER(B6)),0,A7))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=IF(A8="Start Word",A8,IF(OR(A8="End Word",ISNUMBER(B7)),0,A8))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]=IF(A9="Start Word",A9,IF(OR(A9="End Word",ISNUMBER(B8)),0,A9))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=IF(A10="Start Word",A10,IF(OR(A10="End Word",ISNUMBER(B9)),0,A10))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]=IF(A11="Start Word",A11,IF(OR(A11="End Word",ISNUMBER(B10)),0,A11))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]=IF(A12="Start Word",A12,IF(OR(A12="End Word",ISNUMBER(B11)),0,A12))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]=IF(A13="Start Word",A13,IF(OR(A13="End Word",ISNUMBER(B12)),0,A13))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]=IF(A14="Start Word",A14,IF(OR(A14="End Word",ISNUMBER(B13)),0,A14))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Then all you need to do is filter out the 0's and paste where you wish it.

Q