Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

loop, find+delete

Posted on 2011-09-02
11
Medium Priority
?
286 Views
Last Modified: 2012-05-12
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
Comment
Question by:Seamus2626
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 14

Expert Comment

by:JP
ID: 36473194
It is a little unclear, you say stop deleting rows and restart the above process which deletes rows??
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36473201
Seamus - when you restart the process what exactly needs to be done?
0
 
LVL 14

Expert Comment

by:JP
ID: 36473203
OK, I read though it again and I think I know what your asking for. Couple minutes I can shoot you something.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 24

Expert Comment

by:StephenJR
ID: 36473209
Ditto, but will leave to you peetjh!
0
 

Author Comment

by:Seamus2626
ID: 36473238
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
 
LVL 14

Expert Comment

by:JP
ID: 36473269
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
 
LVL 14

Expert Comment

by:JP
ID: 36473279
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
 

Author Comment

by:Seamus2626
ID: 36473343
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
 
LVL 14

Accepted Solution

by:
JP earned 1200 total points
ID: 36473380
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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 36473404
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
 

Author Closing Comment

by:Seamus2626
ID: 36473469
Thanks guys!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question