Excel Macro to delete rows of data with a specific field tag for a trigger and a colon as a stop for deletion

[stressler]

I am looking to build a macro that will delete rows of data from my Excel sheet based on a specific trigger and a specific stop.

Below you will see the format for my data. All rows of data are in column A (single column report), each line of data is in the next row down so it's a sheet that only uses column A and the entire report has about 60,000 lines of data. I have included a few below.

What I am looking to do is use field tag :50 to start the deletion, then delete that row and the following rows of data until the next colon is reached. So basically I need the macro to look for each :, then read the following characters to see if they are 50, if yes, then delete that row of data and continue deleting rows of data until the next : is reached. then start over by reading that colon and the two characters following that : to see if it's 50, if yes, delete through the next colon, but if the next two characters are not 50, then I need it to move to the next colon and read again, deleting if a 50 is found following the colon ":". This must continue down through the entire sheet for column A through the last row.

:50K:/000004010016467

R.S.V.P. INTERNATIONAL, INC.

4021 13TH AVENUE WEST

SEATTLE WA 98119

:56A:FCBKHKHH

:57D:First Commercial Bank Hong Kong Bra

7/F, Hong Kong Club Building

3A Charter Road

Central, HK

:59:/941110625844

Chiu Mi-Hsueh a.k.a. Deborah Chiu

4F.,No 356-1, Fu-Shing North Road

Taipei, Taiwan

:70:IN. 16018

IN. S16018

:50K:/000007219406795

DIRECO INTERNATIONAL LLC

7374 BERKSHIRE DOWNS DR

RALEIGH NC 27616-5636

:56A:COEBLALA

:57D:BANQUE POUR LE COMMERCE EXTERIEUR L

AO PUBLIC

1 PANGKHAM STREET

VIENTIANE,LA

<tbody>
</tbody><colgroup><col></colgroup>

 

[JoeMo]

This assumes that the field tag ":50" will always be at the start of a cell's content with no leading spaces (i.e. the first 3 characters in the cell are :50).

Sub DeleteIf50()
Dim R As Range
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
R.Replace ":50*", "#N/A"
On Error Resume Next
R.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[stressler]

Thanks for this first version of the macro I need. I appreciate your help . When I run it, it deletes the row that the :50 appears in, but doesn't delete the following rows up to the next :. I'm looking for everything in between the :50 and the next colon ":" to be deleted and then for the macro to read the next colon to see if it's a :50 as well and delete or move to the next colon if it's not a :50 tag. Is that something that can be done or am I thinking too big?

 

[JoeMo]

Thanks for this first version of the macro I need. I appreciate your help . When I run it, it deletes the row that the :50 appears in, but doesn't delete the following rows up to the next :. I'm looking for everything in between the :50 and the next colon ":" to be deleted and then for the macro to read the next colon to see if it's a :50 as well and delete or move to the next colon if it's not a :50 tag. Is that something that can be done or am I thinking too big?

Guess I didn't read your OP carefully. See if this revision does what you want:

Sub DeleteIf50()
Dim R As Range, R1 As Range, Ar As Range, i As Long
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
For i = R.Rows.Count To 2 Step -1
    If R(i) Like ":*" Then R(i).EntireRow.Insert
Next i
On Error Resume Next
Set R1 = R.SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0
If Not R1 Is Nothing Then
    MsgBox R.Address
    For Each Ar In R1.Areas
        If Ar(1).Value Like ":50*" Then Ar.EntireRow.Delete
    Next Ar
End If
On Error GoTo 0
R.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[stressler]

This worked extremely well. Thank you for all your help and hard work, this was amazing!!!

 

[JoeMo]

This worked extremely well. Thank you for all your help and hard work, this was amazing!!!

You are welcome - thanks for the reply.