none
Excel loop RRS feed

  • General discussion

  • I am trying to do a loop but my it has been a long while and I just forget. I am trying to run through a 1000 lines of data which the converted pdf leaves notations of pages and headers. The code runs and does what it is supposed to do but at the end, I get a debug error of the Find code. "Object variable or With block variable not set." I guess I need an else or exit but don't have a clue

    Code

     Do While Cells.Select <> " "
        If Cells.Find(what:="----------------------- Page ", After:=ActiveCell, _
            LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate Then
         
        ActiveCell.Rows("1:8").EntireRow.Select
        Selection.Delete Shift:=xlUp
       
        End If

    Your help is greatly appreciated.

    Terri


    Terri

    Monday, May 11, 2015 11:29 PM

All replies

  •  

    Here's how to find, delete, and loop

     Sub TestMacro()
        Dim C As Range
        Dim d As Range

        Set C = Cells.Find(what:="----------------------- Page ", After:=ActiveCell, _
            LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        While Not C Is Nothing
                Set d = C.Offset(8)
                C.Rows("1:8").EntireRow.Delete Shift:=xlUp
                Set C = Cells.FindNext(d)
        Wend
    End Sub

        
    Tuesday, May 12, 2015 2:46 PM