Loop+delete code

Hi,

I need a sub that will loop through Col A until it finds the words "Dividend Breaks"

Once it finds this, go one cell up and delete all the rows back up to the top

E.G. if "Dividend Breaks was on A327, the code would select A326 and delete to the top (A1)

Thanks
Seamus
Seamus2626Asked:
Who is Participating?
 
StephenJRCommented:
Seamus - this deletes entire rows but can be amended:
Sub x()
  
Dim rFind As Range
 
With ActiveSheet.Columns(1)
    Set rFind = .Find(What:="Dividend Breaks", After:=.Range("A1"), LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        Range("A1", rFind.Offset(-1)).EntireRow.Delete
    End If
End With
     
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Magic!!

Thanks Stephen

Seamus
0
 
HainKurtSr. System AnalystCommented:
here it is
Sub delUp()
    Dim c1, c2
    Dim r As Range
    
    Set c1 = Cells(1, 1)
    Set c2 = c1.End(xlDown)
    Set r = Range(c1, c2)
    
    For Each c In r
      If InStr(c.Value, "Dividend Breaks") > 0 Then
        Range(c, c1).Select
        Selection.Delete Shift:=xlUp
        Exit For
      End If
    Next
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Thank you too HainKurt,

Cheers,
Seamus
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.

All Courses

From novice to tech pro — start learning today.