Keep multiple strings and delete the other data

Question

I am facing a minor issue, basically I have successfully done the macro process for one string. But I am unable to process the same vba macro for keeping multiple strings and delete the unwanted data present in the CSV file.

Currently the below code only keeps the rows with string Event Magnitude: and deletes the rest.

However I want to add multiple strings like Event Duration:,
Trigger Date:, Trigger Time: in the same macro and I am unsure how to do it.

IF I can add multiple strings this macro will check for all 4 strings and keep that data and delete the rest of the data.

Sub DeleteNotMIS()
    Dim r As Long, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For r = lr To 2 Step -1
        If InStr(Cells(r, 1), "Event Magnitude: ") = 0 Then Rows(r).Delete
    Next r
 End Sub

If you just want to extract some data of the CSV, I would have a look at [Regular Expressions](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) and process the CSV as text file with a RegEx instead of importing the whole file into Excel. — Pᴇʜ, May 15 '19 at 06:56
@Pᴇʜ could suggest a solution code? or how could i incorporate multiple strings for this code? — j.doe, May 15 '19 at 06:59
Actually that would be to much for this question. But I posted a solution that extends your code below. — Pᴇʜ, May 15 '19 at 07:06

Pᴇʜ · Accepted Answer · 2019-05-15T07:13:10.513

You need to define a list of keywords to keep KeywordsToKeep = Array("Event Magnitude: ", "Trigger Date:") and then check in another loop each keyword

Option Explicit

Public Sub DeleteNotMIS()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Dim KeywordsToKeep() As Variant
    KeywordsToKeep = Array("Event Magnitude: ", "Trigger Date:")  'Add other keywords here

    Dim iRow As Long, eKey As Variant, FoundKey As Boolean
    For iRow = LastRow To 2 Step -1
        FoundKey = False 'initialize
        For Each eKey In KeywordsToKeep
            If InStr(Cells(iRow, 1), eKey) <> 0 Then 
                FoundKey = True
                Exit For 'we don't need to check further keys if we already found one.
            End If
        Next eKey
        If Not FoundKey Then Rows(iRow).Delete
    Next iRow 
End Sub

If each keyword can occur only once in the CSV file …
this approach is slow, because it has to check every row (row by row). A faster approach then would be to use the Range.Find method to find each keyword directly and extract/copy it to a new sheet.

Keep multiple strings and delete the other data

1 Answers1