• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

loop, find+delete

Hi,

I need some code that can loop through Column B and look for

"STANDARD DIVIDEND PAYMENT"

Once found delete that row and move down and keep deleting rows until you find

"NOTICE==================="

Once this is found, delete that row as well but sop deleting rows, then restart the above process until it has looped through the count of ColA

Hope that makes sense!

Thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
JPIT DirectorCommented:
It is a little unclear, you say stop deleting rows and restart the above process which deletes rows??
0
 
StephenJRCommented:
Seamus - when you restart the process what exactly needs to be done?
0
 
JPIT DirectorCommented:
OK, I read though it again and I think I know what your asking for. Couple minutes I can shoot you something.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
StephenJRCommented:
Ditto, but will leave to you peetjh!
0
 
Seamus2626Author Commented:
When i say restart the process i mean start looking for

"STANDARD DIVIDEND PAYMENT"
 again, once found delete the row, and keep deleting until

"NOTICE==================="

and then go again!

Thanks
Seamus
0
 
JPIT DirectorCommented:
Try this code attached. It starts at the bottom and works to the top because when you delete rows they shift up and you would end up skipping rows.
Sub cleanup()
Dim lastrow As Long, noticerow As Long, paymentrow As Long
lastrow = [a65536].End(xlUp).Row
For i = lastrow To 2 Step -1 'Assumes you have a header row. If not change "to 2" to "to 1"
    If Cells(i, "a").Value = "NOTICE===================" Then
        noticerow = i
    End If
    If Cells(i, "a").Value = "STANDARD DIVIDEND PAYMENT" Then
        paymentrow = i
        Rows(paymentrow & ":" & noticerow).Delete (xlUp)
    End If
Next i
End Sub

Open in new window

0
 
JPIT DirectorCommented:
If the number of "=" after NOTICE is not consistent we could replace line 5 with.

If mid(Cells(i, "a").Value,1,6) = "NOTICE" Then
0
 
Seamus2626Author Commented:
That looks good Peetjh, but i get type mismatch errors because on some rows i have #NAME errors, can the code ignore those cells with #NAME?

Thanks
Seamus
0
 
JPIT DirectorCommented:
Revised code attached.
Sub cleanup()
Dim lastrow As Long, noticerow As Long, paymentrow As Long
lastrow = [a65536].End(xlUp).Row
For i = lastrow To 2 Step -1 'Assumes you have a header row. If not change "to 2" to "to 1"
    On Error GoTo continue
    If Cells(i, "a").Value = "NOTICE===================" Then
        noticerow = i
    End If
    If Cells(i, "a").Value = "STANDARD DIVIDEND PAYMENT" Then
        paymentrow = i
        Rows(paymentrow & ":" & noticerow).Delete (xlUp)
    End If
continue:
Next i
End Sub

Open in new window

0
 
Patrick MatthewsCommented:
The following seems to be working for me, using the Find method, which is usually faster than looping through a range:

Sub KillRows()
    
    Dim SRng As Range, DRng As Range
    
    Const SText As String = "STANDARD DIVIDEND PAYMENT"
    Const DText As String = "NOTICE==================="

    Do
        Set SRng = Range("b:b").Find(SText, Range("b" & Rows.Count), xlValues)
        If Not SRng Is Nothing Then
            Set DRng = Range("b:b").Find(DText, SRng, xlValues)
            If Not DRng Is Nothing Then
                Range(SRng, DRng).EntireRow.Delete
            Else
                Exit Do
            End If
        Else
            Exit Do
        End If
    Loop
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now