Run-time error '91': Object variable or With block variable not set

Hi,

Would anyone know how to resolve this error?

It's this part of the code that's causing it.

Selection.Find(What:="********REPORT********", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("A72:I72").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlToLeft

I essentially want to delete all rows including this one (based on the find search) to the end of the sheet,row1048576. It seems the way I have done this has restrictions on the row # (A72:I72) - This would change each day depending on the size of the report. I want to be able to just delete all information based on what row the find criteria is.

I may have 2 issues, 1 being the error 91, and the other for selecting which rows to delete based on what row the find criteria is in.

This is a work around to clean up a report that is in xml format. some companies just aren't up to speed in reporting (no csv, xsl, or text).

Thanks for the help.

You could do with attaching a workbook, however your error 91 is because the find hasn't found anything so you need to handle that, here's a way:

On Error Resume Next
        Selection.Find(What:="********REPORT********", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Range("A72:I72").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlToLeft
On Error GoTo 0

Hi

Try:

Dim rFound As Range

With ActiveSheet
  Set rFound = .Cells.Find(What:="********REPORT********", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

'check if anything found:
    If Not rFound Is Nothing Then  'if it isn't nothing then it's something ie it was found!
        Application.Intersect(.Range(rFound, .Cells(.Rows.Count, .Columns.Count)), .UsedRange).Delete Shift:=xlToLeft
    End If
End With

Richard

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save