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

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
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>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,144
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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).
Code:
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
 
Upvote 0

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
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?
 
Upvote 0

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
18,144
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,195,953
Messages
6,012,518
Members
441,704
Latest member
Perez1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top