A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic

On Jul 1, 10:41*am, Nic Daniels
wrote:
* * * * A * * * B * * * C * * * D * * * E * * * F
1 * * *
2 * * * *TXT * * * * * * * * * * * * * * * * * *
3 * * * * * * * Plot * * * * * 3 * * * * * * * * * * * *
4 * * * TXT * * * * * * * * * * * * * * * * * *
5 * * * * * * * Plot * *0 * * * * * * * * * * *
6 * * * TXT * * * * * * * * * * * * * * x * * *
7 * * * TXT * * * * * * * * * * x * * * * * * *
8 * * * * * * * * * * * * 9.9.A-0002 * * * * * * * * * *
9 * * * * * * * Plot * * * * *A * * * * * * * *
10 * * *TXT * * * * * * * * * * * * * * * * * *
11 * * *TXT * * * * * * * * * * * * * * * * * * * * * *
12 * * * * * * * * * * * * * * * * 9.9.A-0004 *

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks! * * * * * *

--
Kind regards, Nic

Nic,

Here is some code that should get you started.

Best,

Matthew Herbert

Sub FindEntries()
Dim rngLoop As Range
Dim rngCell As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCopy As Range
Dim lngCells As Long
Dim Wks As Worksheet

'assumes your data starts in row one and that
' there is more than one row of data and that
' the first entry is not 9.9xxxxxxx (but you
' can modify this as desired)

Set Wks = ThisWorkbook.Worksheets("Sheet1")

With Wks
lngCells = .Cells(.Cells.Count).Row
Set rngLoop = .Range("C1", .Range("C" & lngCells).End(xlUp))
End With

For Each rngCell In rngLoop.Cells
If IsNumeric(rngCell) And IsEmpty(rngCell) = False Then
Set rngStart = rngCell
End If

If Left(rngCell, 3) = "9.9" Then
Set rngEnd = rngCell.Offset(-1, 0)
Set rngCopy = Range(rngStart, rngEnd)
Set rngStart = Nothing
Set rngEnd = Nothing
End If

If Not rngCopy Is Nothing Then
MsgBox "Copy your range to the desired destination." _
& vbLf & vbLf & rngCopy.Address(external:=True)
Set rngCopy = Nothing
End If
Next

End Sub

Try this code. I gets a little complicated because you don't want the data
for the 1st Plot in row 3. I use a variable first to indicaste where the
word Plot is located and then look for data in row c in the next row.


Sub findplotgroup()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after plot
If First < 0 And _
Range("B" & (RowCount + 1)) < "Plot" Then

Rows(First & ":" & RowCount).Copy
End If
End If
End If
RowCount = RowCount + 1
Loop
End Sub


"Nic Daniels" wrote:

A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic

Thank you so much for helping me!

I tried your code and I think I was a bit unclear about what I wanted. The
code is a very good start though, thanks. My original idea was to move rows
5-7 and 9-11 to another work book and before that tagging them with their
ID-number, because there are quite many entries in my work book (not just the
example I gave you).

So for instance, row 9-11 should have a tag saying 9.9.A-0002 (always the
closest ID-number, the one "above"). So the result in another workbook would
be:

9.9.A-0002 Plot A
TXT
TXT



Do you think that's possible? Another thing, the text "Plot" can be another
frase, mayby you could use the fact that there is always an emty cell in
column A as the final step going upwards from the ID-number.




TXT
Plot 3
TXT
Plot 0
TXT
TXT
9.9.A-0002
Plot A
TXT
TXT
9.9.A-0004



Plot B
TXT
TXT
9.9.A-1234


Your code includes the ID-numbers in the copy,
--
Kind regards, Nic


"Joel" wrote:

Try this code. I gets a little complicated because you don't want the data
for the 1st Plot in row 3. I use a variable first to indicaste where the
word Plot is located and then look for data in row c in the next row.


Sub findplotgroup()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after plot
If First < 0 And _
Range("B" & (RowCount + 1)) < "Plot" Then

Rows(First & ":" & RowCount).Copy
End If
End If
End If
RowCount = RowCount + 1
Loop
End Sub


"Nic Daniels" wrote:

A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic

Hi
The last message was sent by accident, Im sorry about that. So, Im looking
for a code that copies the ID-number into another column (into another
workbook OR in the same workbook but just a little bit to the right) and the
specific rows we talked about before. The rows are the ones going from the
row above the next ID-number going up until you reach an empty cell in column
A, that row should also be included. There are some x:s in some columns to
the right (as you can see) that have to be included. There is no pattern that
you can use (they are functions of keywords in the text in the same row just
so you know).

Ex.

TXT1
9.9.A-0002
PlotU A
TXT2 x
TXT3 x
9.9.A-0004

TXT4 PlotV 1 x

PlotZ B
TXT5 x
TXT6 x
9.9.A-1234

Etc€¦
Result:
9.9.A-0002 PlotU A
TXT2
TXT3

9.9.A-0004 PlotZ B
TXT5
TXT6
9.9.A-1234

Etc€¦
Thank you!
--
Kind regards, Nic


"Nic Daniels" wrote:

Thank you so much for helping me!

I tried your code and I think I was a bit unclear about what I wanted. The
code is a very good start though, thanks. My original idea was to move rows
5-7 and 9-11 to another work book and before that tagging them with their
ID-number, because there are quite many entries in my work book (not just the
example I gave you).

So for instance, row 9-11 should have a tag saying 9.9.A-0002 (always the
closest ID-number, the one "above"). So the result in another workbook would
be:

9.9.A-0002 Plot A
TXT
TXT



Do you think that's possible? Another thing, the text "Plot" can be another
frase, mayby you could use the fact that there is always an emty cell in
column A as the final step going upwards from the ID-number.




TXT
Plot 3
TXT
Plot 0
TXT
TXT
9.9.A-0002
Plot A
TXT
TXT
9.9.A-0004



Plot B
TXT
TXT
9.9.A-1234


Your code includes the ID-numbers in the copy,
--
Kind regards, Nic


"Joel" wrote:

Try this code. I gets a little complicated because you don't want the data
for the 1st Plot in row 3. I use a variable first to indicaste where the
word Plot is located and then look for data in row c in the next row.


Sub findplotgroup()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after plot
If First < 0 And _
Range("B" & (RowCount + 1)) < "Plot" Then

Rows(First & ":" & RowCount).Copy
End If
End If
End If
RowCount = RowCount + 1
Loop
End Sub


"Nic Daniels" wrote:

A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic

I created a new worksheet in the current workbook and used Sheet1 as the
source sheet. Change as required

Sub findplotgroup()

Set oldsht = Sheets("Sheet1")
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))

With oldsht
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If .Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If .Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after
plot
If First < 0 And _
.Range("B" & (RowCount + 1)) < "Plot" Then

LastRow = NewSht.Range("C" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NewSht.Range("A" & NewRow) = .Range("C" & (First - 1))
NewSht.Range("B" & NewRow) = .Range("B" & First)
NewSht.Range("C" & NewRow) = .Range("C" & First)
.Range("A" & (First + 1) & ":E" & RowCount).copy _
destination:=NewSht.Range("B" & (NewRow + 1))

End If
End If
End If
RowCount = RowCount + 1
Loop
End With
End Sub





"Nic Daniels" wrote:

Hi
The last message was sent by accident, Im sorry about that. So, Im looking
for a code that copies the ID-number into another column (into another
workbook OR in the same workbook but just a little bit to the right) and the
specific rows we talked about before. The rows are the ones going from the
row above the next ID-number going up until you reach an empty cell in column
A, that row should also be included. There are some x:s in some columns to
the right (as you can see) that have to be included. There is no pattern that
you can use (they are functions of keywords in the text in the same row just
so you know).

Ex.

TXT1
9.9.A-0002
PlotU A
TXT2 x
TXT3 x
9.9.A-0004

TXT4 PlotV 1 x

PlotZ B
TXT5 x
TXT6 x
9.9.A-1234

Etc€¦
Result:
9.9.A-0002 PlotU A
TXT2
TXT3

9.9.A-0004 PlotZ B
TXT5
TXT6
9.9.A-1234

Etc€¦
Thank you!
--
Kind regards, Nic


"Nic Daniels" wrote:

Thank you so much for helping me!

I tried your code and I think I was a bit unclear about what I wanted. The
code is a very good start though, thanks. My original idea was to move rows
5-7 and 9-11 to another work book and before that tagging them with their
ID-number, because there are quite many entries in my work book (not just the
example I gave you).

So for instance, row 9-11 should have a tag saying 9.9.A-0002 (always the
closest ID-number, the one "above"). So the result in another workbook would
be:

9.9.A-0002 Plot A
TXT
TXT



Do you think that's possible? Another thing, the text "Plot" can be another
frase, mayby you could use the fact that there is always an emty cell in
column A as the final step going upwards from the ID-number.




TXT
Plot 3
TXT
Plot 0
TXT
TXT
9.9.A-0002
Plot A
TXT
TXT
9.9.A-0004



Plot B
TXT
TXT
9.9.A-1234


Your code includes the ID-numbers in the copy,
--
Kind regards, Nic


"Joel" wrote:

Try this code. I gets a little complicated because you don't want the data
for the 1st Plot in row 3. I use a variable first to indicaste where the
word Plot is located and then look for data in row c in the next row.


Sub findplotgroup()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after plot
If First < 0 And _
Range("B" & (RowCount + 1)) < "Plot" Then

Rows(First & ":" & RowCount).Copy
End If
End If
End If
RowCount = RowCount + 1
Loop
End Sub


"Nic Daniels" wrote:

A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic

Hi,

Thank you. Unfortunately, I can't get it to work since the code still
depends on row B containing the word "plot". The word in column B could be
another word:

Ex:


9.9.A-0002
Station A
TXT2 x
TXT3 x
9.9.A-0004

TXT4 PlotStar 1 x

Inter B
TXT5 x
TXT6 x
9.9.A-0005
Etc..........


Result:
9.9.A-0002 Station A
TXT2 x
TXT3 x
9.9.A-0004 Inter B
TXT5 x
TXT6 x
Etc...............

--

I hope you can find a solution, it would make the data handling much easier.
Kind regards, Nic


"Joel" wrote:

I created a new worksheet in the current workbook and used Sheet1 as the
source sheet. Change as required

Sub findplotgroup()

Set oldsht = Sheets("Sheet1")
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))

With oldsht
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If .Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If .Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after
plot
If First < 0 And _
.Range("B" & (RowCount + 1)) < "Plot" Then

LastRow = NewSht.Range("C" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NewSht.Range("A" & NewRow) = .Range("C" & (First - 1))
NewSht.Range("B" & NewRow) = .Range("B" & First)
NewSht.Range("C" & NewRow) = .Range("C" & First)
.Range("A" & (First + 1) & ":E" & RowCount).copy _
destination:=NewSht.Range("B" & (NewRow + 1))

End If
End If
End If
RowCount = RowCount + 1
Loop
End With
End Sub





"Nic Daniels" wrote:

Hi
The last message was sent by accident, Im sorry about that. So, Im looking
for a code that copies the ID-number into another column (into another
workbook OR in the same workbook but just a little bit to the right) and the
specific rows we talked about before. The rows are the ones going from the
row above the next ID-number going up until you reach an empty cell in column
A, that row should also be included. There are some x:s in some columns to
the right (as you can see) that have to be included. There is no pattern that
you can use (they are functions of keywords in the text in the same row just
so you know).

Ex.

TXT1
9.9.A-0002
PlotU A
TXT2 x
TXT3 x
9.9.A-0004

TXT4 PlotV 1 x

PlotZ B
TXT5 x
TXT6 x
9.9.A-1234

Etc€¦
Result:
9.9.A-0002 PlotU A
TXT2
TXT3

9.9.A-0004 PlotZ B
TXT5
TXT6
9.9.A-1234

Etc€¦
Thank you!
--
Kind regards, Nic


"Nic Daniels" wrote:

Thank you so much for helping me!

I tried your code and I think I was a bit unclear about what I wanted. The
code is a very good start though, thanks. My original idea was to move rows
5-7 and 9-11 to another work book and before that tagging them with their
ID-number, because there are quite many entries in my work book (not just the
example I gave you).

So for instance, row 9-11 should have a tag saying 9.9.A-0002 (always the
closest ID-number, the one "above"). So the result in another workbook would
be:

9.9.A-0002 Plot A
TXT
TXT



Do you think that's possible? Another thing, the text "Plot" can be another
frase, mayby you could use the fact that there is always an emty cell in
column A as the final step going upwards from the ID-number.




TXT
Plot 3
TXT
Plot 0
TXT
TXT
9.9.A-0002
Plot A
TXT
TXT
9.9.A-0004



Plot B
TXT
TXT
9.9.A-1234


Your code includes the ID-numbers in the copy,
--
Kind regards, Nic


"Joel" wrote:

Try this code. I gets a little complicated because you don't want the data
for the 1st Plot in row 3. I use a variable first to indicaste where the
word Plot is located and then look for data in row c in the next row.


Sub findplotgroup()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after plot
If First < 0 And _
Range("B" & (RowCount + 1)) < "Plot" Then

Rows(First & ":" & RowCount).Copy
End If
End If
End If
RowCount = RowCount + 1
Loop
End Sub


"Nic Daniels" wrote:

A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic

Try this. Your example keep on changing which requires alight modifications
of the code.

Sub findplotgroup()

Set oldsht = Sheets("Sheet1")
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))

With oldsht
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If .Range("B" & RowCount) = "" And _
.Range("C" & RowCount) < "" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If .Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after
'plot
If First < 0 Then

LastRow = NewSht.Range("C" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NewSht.Range("A" & NewRow) = .Range("C" & First)
NewSht.Range("B" & NewRow) = .Range("B" & (First + 1))
NewSht.Range("C" & NewRow) = .Range("C" & (First + 1))
.Range("B" & (First + 2) & ":F" & RowCount).Copy _
Destination:=NewSht.Range("B" & (NewRow + 1))

End If
End If
End If
RowCount = RowCount + 1
Loop
End With
End Sub




"Nic Daniels" wrote:

Hi,

Thank you. Unfortunately, I can't get it to work since the code still
depends on row B containing the word "plot". The word in column B could be
another word:

Ex:


9.9.A-0002
Station A
TXT2 x
TXT3 x
9.9.A-0004

TXT4 PlotStar 1 x

Inter B
TXT5 x
TXT6 x
9.9.A-0005
Etc..........


Result:
9.9.A-0002 Station A
TXT2 x
TXT3 x
9.9.A-0004 Inter B
TXT5 x
TXT6 x
Etc...............

--

I hope you can find a solution, it would make the data handling much easier.
Kind regards, Nic


"Joel" wrote:

I created a new worksheet in the current workbook and used Sheet1 as the
source sheet. Change as required

Sub findplotgroup()

Set oldsht = Sheets("Sheet1")
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))

With oldsht
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If .Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If .Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after
plot
If First < 0 And _
.Range("B" & (RowCount + 1)) < "Plot" Then

LastRow = NewSht.Range("C" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
NewSht.Range("A" & NewRow) = .Range("C" & (First - 1))
NewSht.Range("B" & NewRow) = .Range("B" & First)
NewSht.Range("C" & NewRow) = .Range("C" & First)
.Range("A" & (First + 1) & ":E" & RowCount).copy _
destination:=NewSht.Range("B" & (NewRow + 1))

End If
End If
End If
RowCount = RowCount + 1
Loop
End With
End Sub





"Nic Daniels" wrote:

Hi
The last message was sent by accident, Im sorry about that. So, Im looking
for a code that copies the ID-number into another column (into another
workbook OR in the same workbook but just a little bit to the right) and the
specific rows we talked about before. The rows are the ones going from the
row above the next ID-number going up until you reach an empty cell in column
A, that row should also be included. There are some x:s in some columns to
the right (as you can see) that have to be included. There is no pattern that
you can use (they are functions of keywords in the text in the same row just
so you know).

Ex.

TXT1
9.9.A-0002
PlotU A
TXT2 x
TXT3 x
9.9.A-0004

TXT4 PlotV 1 x

PlotZ B
TXT5 x
TXT6 x
9.9.A-1234

Etc€¦
Result:
9.9.A-0002 PlotU A
TXT2
TXT3

9.9.A-0004 PlotZ B
TXT5
TXT6
9.9.A-1234

Etc€¦
Thank you!
--
Kind regards, Nic


"Nic Daniels" wrote:

Thank you so much for helping me!

I tried your code and I think I was a bit unclear about what I wanted. The
code is a very good start though, thanks. My original idea was to move rows
5-7 and 9-11 to another work book and before that tagging them with their
ID-number, because there are quite many entries in my work book (not just the
example I gave you).

So for instance, row 9-11 should have a tag saying 9.9.A-0002 (always the
closest ID-number, the one "above"). So the result in another workbook would
be:

9.9.A-0002 Plot A
TXT
TXT



Do you think that's possible? Another thing, the text "Plot" can be another
frase, mayby you could use the fact that there is always an emty cell in
column A as the final step going upwards from the ID-number.




TXT
Plot 3
TXT
Plot 0
TXT
TXT
9.9.A-0002
Plot A
TXT
TXT
9.9.A-0004



Plot B
TXT
TXT
9.9.A-1234


Your code includes the ID-numbers in the copy,
--
Kind regards, Nic


"Joel" wrote:

Try this code. I gets a little complicated because you don't want the data
for the 1st Plot in row 3. I use a variable first to indicaste where the
word Plot is located and then look for data in row c in the next row.


Sub findplotgroup()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
First = 0
RowCount = 1
Do While RowCount <= LastRow
If Range("B" & RowCount) = "Plot" Then
First = RowCount
Else
'check if data exists in the next row in column C or
'when the last row is reached.
If Range("C" & (RowCount + 1)) < "" Or _
RowCount = LastRow Then

'don't look at any data into the 1st plot is found
'and ignore any rows where the isn't any data in column c after plot
If First < 0 And _
Range("B" & (RowCount + 1)) < "Plot" Then

Rows(First & ":" & RowCount).Copy
End If
End If
End If
RowCount = RowCount + 1
Loop
End Sub


"Nic Daniels" wrote:

A B C D E F
1
2 TXT
3 Plot 3
4 TXT
5 Plot 0
6 TXT x
7 TXT x
8 9.9.A-0002
9 Plot A
10 TXT
11 TXT
12 9.9.A-0004

Hi,

I'd like to filter some rows using some criteria. Once you reach an
ID-number in row c with an ID-number, 9.9.A-XXXX (X could be any number) I'd
like to make a copy of the row above and the next one and so on until you
reach a number or a letter in row c (the cells inbetween are empty as you can
see). The row with a number or letter has to be included in the copy. In this
case the copy would be 5-7 and 9-11.It would be great if the copied material
would be automatically updated if any modifications of the source file were
made. I would really appreciate any help I can get. Thanks!

--
Kind regards, Nic