Filtering using critera

[Nic Daniels]

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!

 

[Matthew Herbert]

        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 youcan
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!            

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

 

[Joel]

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]

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...............

 

[Joel]

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