Thread: Finding and copying rows that meet key word criteria

Hi all.

I am attempting to parse through 1000's rows of data to copy 3 specific rows that correspond to specific object (i.e., Dune Field). Each Dune field has a row that starts with "ROI", "Band 1", and "Band 2" that are irregularly spaced due to different dune field sizes. So for each of ~100+ dune fields I only need these three rows copied to another sheet.

I followed this post:
http://www.excelforum.com/excel-gene...ing-cells.html
which is really close, but differ because I need: 1) to search for three key words, 2) copy all three rows and keep them in that order, and 3) the first criteria needs a wildcard for the "ROI" row as it has text after it (i.e., it is not a one word cell, but something like "ROI: EVF: Layer: Dune_Field.shp (Dune_ID=0597+369) [White] 85 points")

Thanks much, and I have included a sample of my data to be sorted?
MC

MC;
what dangelor is saying:
1) Create another column and put #s in that column 1.....64,565.
2) Sort the data in any way that gives you the results you want.
3) Sort on the added column to put your data back the way it was.
4) Optional delete the added column.

I'll look at your workbook and see if there is a way to have what you want done automatically.

MC;
If dangelor's solution works mark the thread solved.

If not, give me an example of what you want to find, and an example of the way you want the results to look like.
Do the results need to be on a different sheet, or can they just be at the top of the sheet to the right of the data?
Do you want the found rows deleted from the data?
Do you want to be able to do this over and over? or is this a one time only event?

After I have sorted A-Z I would like to quickly delete all rows not meeting the criteria of three desired rows ("ROI", "Band 1", and "Band 2")?

I never quite understood how the criteria works. You would have to give me an example.

Also how do you delete rows with out them coming back?

I don't understand what you mean by "coming back". Once a row is deleted, it's gone. It can't come back.
Unless you're using the wrong word. Walk though the steps you take to "delete" a row(s), so I can see what you're talking about.

I have to leave for a couple of hours. I'll look at it when I get back.

Here's a VBA procedure to accomplish the task.

Sub Delete_Rows()

    Dim vData As Variant
    Dim x As Long
    Dim y As Integer
    Dim str1 As String
    Dim str2 As String
    Dim str3 As String

    'Capture activesheet's data
    vData = Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell))

    'Create search strings
    str1 = "ROI"
    str2 = "Band 1"
    str3 = "Band 2"

    'Search first column for match
    For x = LBound(vData, 1) To UBound(vData, 1)
        If InStr(vData(x, 1), str1) = 0 Then
            If InStr(vData(x, 1), str2) = 0 Then
                If InStr(vData(x, 1), str3) = 0 Then

                    'Search failed - remove entire row's data
                    For y = LBound(vData, 2) To UBound(vData, 2)
                        vData(x, y) = Empty
                    Next y

                End If
            End If
        End If
    Next x

    'Replace activesheet data with updated data
    Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell)) = vData

    'Delete rows with no data in first column
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

You will always have 1048576 rows. When you delete any row Excel adds a blank row at the bottom.
1 thing you can do to help keep file size down:
Click CTRL+END. That will take you to the bottom right cell of usedrange. If it's below or to the right of your data then delete all the rows below your data (like you were doing) and columns to the right of your data. Then save and close the file. That gets rid of rows & columns that Excel thought you were using.

If you do a search in the forum for "File Size", I'm sure you will find other suggestions.