[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 2
1 Solution
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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