Delete rows with 3 or more semicolons

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Delete rows with 3 or more semicolons

  1. #1
    Board Regular imimin's Avatar
    Join Date
    May 2006
    Posts
    404
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete rows with 3 or more semicolons

     
    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!

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

    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

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Location
    France Alsace
    Posts
    1,104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

    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

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,581
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

    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.

    Semicolons

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


    Excel tables to the web >> Excel Jeanie HTML 4



    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.


    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
    Last edited by Peter_SSs; Oct 12th, 2008 at 07:19 AM. Reason: Forgot screen shot
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  5. #5
    Board Regular imimin's Avatar
    Join Date
    May 2006
    Posts
    404
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

    Quote Originally Posted by PCL View Post
    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.

  6. #6
    Board Regular imimin's Avatar
    Join Date
    May 2006
    Posts
    404
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

    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

  7. #7

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

    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

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,581
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Delete rows with 3 or more semicolons

      
    Quote Originally Posted by imimin View Post
    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.


    Sub DeleteRowsContaining3Semicolons()
        Dim rng As Range
        Dim fwhat As String
        Dim lr As Long, lc As Long
        
        fwhat = "*;*;*;*"
        Application.ScreenUpdating = False
        lr = ActiveSheet.UsedRange.Rows.Count
        lc = ActiveSheet.UsedRange.Columns.Count
        Do
            Set rng = Range("A1", Cells(lr, lc)).Find(what:=fwhat, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            If Not rng Is Nothing Then
                lr = rng.Row
                Rows(rng.Row).Delete
            End If
        Loop While Not rng Is Nothing
        Application.ScreenUpdating = True
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com