Solved

loop, find+delete

Posted on 2011-09-02
11
238 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:peetjh
Comment Utility
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
Comment Utility
Seamus - when you restart the process what exactly needs to be done?
0
 
LVL 14

Expert Comment

by:peetjh
Comment Utility
OK, I read though it again and I think I know what your asking for. Couple minutes I can shoot you something.
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Ditto, but will leave to you peetjh!
0
 

Author Comment

by:Seamus2626
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:peetjh
Comment Utility
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:peetjh
Comment Utility
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
Comment Utility
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:
peetjh earned 300 total points
Comment Utility
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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
Comment Utility
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
Comment Utility
Thanks guys!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now