Delete rows with 3 or more semicolons

imimin

Active Member
Joined
May 9, 2006
Messages
404
I need to create a macro in Excel that will search every row of a sheet for 3 or more semicolons (";"). Each semicolon will be preceded and proceeded by text. If it finds a row with 3 or more semicolons listed it should delete that row. What I have so far is the following (works fine finding one semicolon, can't seem to figure out syntax for 3 or more):

Code:
Sub DeleteRowsContaining()
Dim rng As Range
Dim what As String
what = ";"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

Thank you!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello imimin,

Here is a macro to delete any row with 3 or more semi-colons. The semi-colons are counted only one per cell.
Code:
Sub DeleteRowsContaining()

 Dim Cnt As Long
 Dim FirstAddx As String
 Dim RowRng As Range
 Dim RngResult As Range
 
   For Each RowRng In ActiveSheet.UsedRange.Rows
     A = RowRng.Address
     Set RngResult = RowRng.Find(What:=";", _
                                 LookIn:=xlValues, _
                                 LookAt:=xlPart, _
                                 SearchOrder:=xlRows, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False)
     If Not RngResult Is Nothing Then
       Cnt = 0
       FirstAddx = RngResult.Address
         Do
           Cnt = Cnt + 1
           Set RngResult = RowRng.FindNext(RngResult)
         Loop While RngResult.Address <> FirstAddx And Not RngResult Is Nothing
     End If
     If Cnt > 2 Then RowRng.Delete Shift:=xlShiftUp
  Next RowRng
  
End Sub
Sincerely,
Leith Ross
 
Upvote 0
Perhaps next code could help.
You could need to use Find Next to scan the complete sheet
Code:
Sub DeleteRowsContaining()
Dim rng As Range
Dim MyRow As Range
Dim F As Object
Dim what As String
Dim Counter As Integer
    what = ";"
    Do
    Set rng = ActiveSheet.UsedRange.Find(what)
    If rng Is Nothing Then
        Exit Do
        Else
            Set MyRow = Range(Cells(rng.Row, 1), Cells(rng.Row, Columns.Count).End(xlToLeft))
            Counter = 0
            For Each F In MyRow
                Counter = Counter + Len(F.Value) - Len(Replace(F.Value, ";", ""))
            Next F
        If (Counter >= 3) Then Rows(rng.Row).Delete
    End If
    Loop
End Sub
 
Upvote 0
It is not exactly clear whether the 3 semicolons are in the same cell or not. I have assumed thay are so that in the sheet below, row 1 would be deleted, but row 2 would not.

Excel Workbook
ABCDEFGH
1x;cvb;w;e4
2a;b;c;d
3
Semicolons




I have also assumed that if there are 3 or more semicolons in acell, then that row would be deleted.

Anyway, try this on a copy of your file.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DeleteRowsContaining3Semicolons()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> what <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    what = "*;*;*;*"<br>    <SPAN style="color:#00007F">Do</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = ActiveSheet.UsedRange.Find(what)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Rows(rng.Row).Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
Perhaps next code could help.
You could need to use Find Next to scan the complete sheet
Code:
Sub DeleteRowsContaining()
Dim rng As Range
Dim MyRow As Range
Dim F As Object
Dim what As String
Dim Counter As Integer
    what = ";"
    Do
    Set rng = ActiveSheet.UsedRange.Find(what)
    If rng Is Nothing Then
        Exit Do
        Else
            Set MyRow = Range(Cells(rng.Row, 1), Cells(rng.Row, Columns.Count).End(xlToLeft))
            Counter = 0
            For Each F In MyRow
                Counter = Counter + Len(F.Value) - Len(Replace(F.Value, ";", ""))
            Next F
        If (Counter >= 3) Then Rows(rng.Row).Delete
    End If
    Loop
End Sub

CAUTION: This code causes my Excel to freeze. Most likely some kind of endless loop.
 
Upvote 0
Peter - the 3 or more semicolons I am looking for ARE IN THE SAME CELL. Your code below works well, but is slow (takes over a minute to process 3500 rows). Is it possible to speed it up?

Thank You!

Sub DeleteRowsContaining3Semicolons()
Dim rng As Range
Dim what As String

what = "*;*;*;*"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If Not rng Is Nothing Then
Rows(rng.Row).Delete
End If
Loop While Not rng Is Nothing
End Sub
 
Upvote 0
Hi,

May be.

Code:
Sub kTest()
Dim a, i As Long, txt As String, c    As Long
Application.ScreenUpdating = 0
a = Range("a1").CurrentRegion
For i = 1 To UBound(a, 1)
    For c = 1 To UBound(a, 2)
        If InStr(1, a(i, c), ";") >= 2 Then
            txt = txt & "," & "a" & i
            If Len(txt) > 245 Then
                txt = Mid$(txt, 2)
                Range(txt).EntireRow.Delete
                txt = ""
            End If
            Exit For
        End If
    Next
Next
If Len(txt) > 1 Then
    txt = Mid$(txt, 2)
    Range(txt).EntireRow.Delete
    txt = ""
End If
Application.ScreenUpdating = 1
End Sub
 
Upvote 0
Peter - the 3 or more semicolons I am looking for ARE IN THE SAME CELL. Your code below works well, but is slow (takes over a minute to process 3500 rows). Is it possible to speed it up?

Thank You!
See if this is better.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DeleteRowsContaining3Semicolons()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> fwhat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    fwhat = "*;*;*;*"<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    lr = ActiveSheet.UsedRange.Rows.Count<br>    lc = ActiveSheet.UsedRange.Columns.Count<br>    <SPAN style="color:#00007F">Do</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1", Cells(lr, lc)).Find(what:=fwhat, _<br>                    SearchOrder:=xlByRows, SearchDirection:=xlPrevious)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            lr = rng.Row<br>            Rows(rng.Row).Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top