CODEWITHSUNDEEP
excelvba
Kotune
Kotune

Reputation: 23

VBA Excel - Delete 2 rows above the row with a specific keyword

I have little insight in VBA, hence I need someone to help me with a script to achieve the outcome as stated in title.

I need to extract specific rows from a log file that I have.

Excerpt from actual log file in excel:

=====================================

Encrypted file: C:\[specific path]      
Algorithm picked for decryption: RC4        
Status: Successfully decrypted!     

Encrypted file: C:\[specific path]          
File looks like it is not encrypted. Skipping ...       

Encrypted file: C:\[specific path]      
File could not be decrypted properly. Skipping ...      

=====================================

It goes like this for about 90k rows. I need to get rid of all "Status: Successfully decrypted!" rows with 2 rows above it. The final data that I need are only rows with skipped paths, that failed decryption.

Each block of text in this log is followed by blank row.

Tried the following one:

Sub DeleteRowsBelow()

Dim x As Long
Dim y As Long

x = ActiveSheet.UsedRange.Rows.Count

Cells.Find(What:="Macro", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Select
y = Selection.Row + 1
Rows(x & ":" & y).EntireRow.Delete

End Sub

But it deletes all of the rows below a certain keyword, so that does not work for me.

Then tried to incorporate the following into the above script:

Rows("1:" & Cells.Find("KEY WORD").Row - 1).Delete

But also with little luck, and again it deletes all of the rows above a certain keyword.

Upvotes: 2

Views: 3088

Answers (1)

user1274820
user1274820

Reputation: 8144

Not sure if this is what you're looking for, but maybe it will help

Just select the cell at the top of the list and run the macro

Sub DeleteSuccessfulRows()

Application.ScreenUpdating = False
Dim x
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
    If Cells(x, 1) = "Status: Successfully decrypted!" Then 'If we find this text
        Cells(x, 1).EntireRow.Delete      'Delete the entire row
        Cells(x - 1, 1).EntireRow.Delete  'Delete the row above it
        Cells(x - 2, 1).EntireRow.Delete  'Delete the row 2 rows above it
        x = x - 2
    'Delete blank rows
    ElseIf Cells(x, 1) = vbNullString Then Cells(x, 1).EntireRow.Delete
    'Optional delete rows that contain "File looks like ..."
    'ElseIf Cells(x, 1) = "File looks like it is not encrypted. Skipping ..." Then Cells(x, 1).EntireRow.Delete
    'ElseIf Cells(x, 1) = "File could not be decrypted properly. Skipping ..." Then Cells(x, 1).EntireRow.Delete
    End If
Next x
Application.ScreenUpdating = True

End Sub

Results:

After

Upvotes: 1

Related Questions