Delete rows with 3 or more semicolons

imimin

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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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
PCL said:
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
Click to expand...

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
Click to expand...
 
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
imimin said:
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!
Click to expand...
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
You must log in or register to reply here.

Similar threads

S
  • Solved
Delete rows that contain highlighting
Replies
5
Views
85
Serafin54
S
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
269
Umma123
U
C
  • Solved
Searching a sheet and displaying on a userform
2
Replies
16
Views
307
colinharwood
C
M
  • Solved
VBA to delete cells from columns preceding the lowest value in the column
Replies
5
Views
97
DanteAmor
DanteAmor
S
  • Solved
Delete Row if value in column C is a duplicate
Replies
8
Views
273
igold
igold

Forum statistics

Threads
1,223,832
Messages
6,174,905
Members
452,590
Latest member
CraiginColorado

Share this page

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