VBA: Delete contents of a sheet after appearance of a string

[Drueckeberger]

Hi,

I am new to this forum and need your professional help. Thank you in advance

Here is my problem:

I have a sheet, lets call it "Statement". In this sheet I have to copy different data sets that always have the same structure. However the amount of rows varies massively. Sometimes the statement data can be 10000 lines, sometimes only 5. Also there are two versions of data sets. So two different structures.

What I want to achieve is that when I run the marco, that all content or all rows after the appearance of a specific string are deleted.
My problem is, that sometimes that string A and String B can either be in A10000 or A5 or A823 (but always column A).

So basically:

• when running the macro
• find string A or string b in column A
• then delete all content/rows below

(string A and B can simply be defined in the module, as they are always the same)

I think this is easy for you, unfortunately not for me

Thanks for you help.

 

[Tim_Excel_]

Sub DeleteRows()

With Workbooks("REFERENCE").Sheets("REFERENCE")

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

StrA = "example1"
StrB = "example2"
Set FindStr = .Range("A:A").Find(StrA)
If FindStr is Nothing Then Set FindStr = .Range("A:A").Find(StrB)
If not FindStr Is Nothing Then 
If FindStr <> LRow Then 
.Rows(FindStr.Row & ":" & LRow).EntireRow.Delete
End If
End If

End with
End Sub

Alternatively, if you want to find the exact strings in the cells.. (instead of searching for e.g. "A" and deleting all rows from the first cell that contains "A")

Sub DeleteRows()

With Workbooks("REFERENCE").Sheets("REFERENCE")

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

StrA = "example1"
StrB = "example2"
Set FindStr = .Range("A:A").Find(StrA, lookat:=xlwhole, matchcase=True)
If FindStr is Nothing Then Set FindStr = .Range("A:A").Find(StrB, lookat:=xlwhole, matchcase=True)
If not FindStr Is Nothing Then 
If FindStr <> LRow Then
.Rows(FindStr.Row& ":" & LRow).EntireRow.Delete
End If
End If

End with
End Sub

 

[Drueckeberger]

Thank you very much. It works like a charm.

 

[Tim_Excel_]

Glad to have helped!