Macro Delete Rows

[John_JingleHymerSmith]

Hi All,
I am still fairly new to Macros and VBA however I am learning from Mr. Excel's 2013 VBA and Macros book. I am attempting to write a macro (see below) that will Loop through the current workbook sheet by sheet and find the specific text to derive Top of Range and Bottom of Range, deliver the row values and then resize the top of the range value by the difference and delete the rows. I have gotten so far however when I attempt to incorporate an IF statement to account for errors (i.e. some worksheets in the workbook will not have the Find Text string I am looking for in either top or bottom) I get the error shown in the comments I placed below. I would like to understand why this is occurring and what am I doing wrong? As a kicker, I would also like this to then take the current worksheet copy to a new workbook then jump back and continue to loop through the sheets in the workbook. It may be more efficient to loop through all then perform the loop to open in new workbook? Appreciate the help and a learning moment!

Sub AutoDelete( )
Dim TopRow As Range
Dim BottomRow As Range
Dim wsCount As Integer
Dim i As Integer

wsCount = ActiveWorkbook.Worksheets.Count

For i = 1 To wsCount
With Worksheets(i)
Set TopRow = .Find("Latest Valuation", LookIn:=xlValues)
Set BottomRow = .Find("Key Areas of Focus", LookIn:=xlValues)

If Not TopRow Or BottomRow Is Nothing Then 'Error Object variable or With block variable not set
TopRowNum = TopRow.Row
BottomRowNum = BottomRow.Row
Worksheets(i).Resize(BottomRowNum - TopRowNum - 1).EntireRow.Delete
End If
Next i

'Additional code to follow to move all sheets finished in loop above to new workbook
End Sub

 

[Alex Blakenburg]

Do you want to delete everything from the Top row to the Bottom row including the rows with the words Latest Valuation & Key Areas of Focus ?

 

[Alex Blakenburg]

Assuming the answer to the question is Yes.
Let's start with this:

Sub AutoDelete()
    Dim TopRow As Range, BottomRow As Range
    Dim TopRowNum As Long, BottomRowNum As Long
    Dim wsCount As Integer
    Dim i As Integer
    
    wsCount = ActiveWorkbook.Worksheets.Count
    
    For i = 1 To wsCount
        With Worksheets(i).Cells
            Set TopRow = .Find(what:="Latest Valuation", LookIn:=xlValues)
            Set BottomRow = .Find(what:="Key Areas of Focus", LookIn:=xlValues)
            
            If Not TopRow Is Nothing And Not BottomRow Is Nothing Then
                TopRowNum = TopRow.Row
                BottomRowNum = BottomRow.Row
                Worksheets(i).Rows(TopRowNum & ":" & BottomRowNum).Delete
            End If
        End With
    Next i
    
    'Additional code to follow to move all sheets finished in loop above to new workbook
End Sub

 

[John_JingleHymerSmith]

Do you want to delete everything from the Top row to the Bottom row including the rows with the words Latest Valuation & Key Areas of Focus ?

Only the Top Row "Latest Valuation" the Bottom Row (Key Areas of Focus) minus 1 gives me the resizing property dimension on the row. Hence I want the row above the bottom row aka I need to keep Key Area of Focus row. The code works if I do not throw it in a loop and just perform it on each sheet, however I would like to understand why I am getting the error in the nested IF statement for TopRow and BottomRow. Appreciate your help! I think its because the object TopRow or BottomRow is a range class but not sure after that

 

[Alex Blakenburg]

In what I just sent you change the "BottomRowNum =" line to add "- 1" see below

BottomRowNum = BottomRow.Row - 1

 

[John_JingleHymerSmith]

In what I just sent you change the "BottomRowNum =" line to add "- 1" see below

BottomRowNum = BottomRow.Row - 1

Okay will give it a shot. Also would you be able to help me understand what is driving the error or what mistake did I make in the code or where was my misunderstanding?

 

[Alex Blakenburg]

Also would you be able to help me understand what is driving the error or what mistake did I make in the code or where was my misunderstanding?

OK changes made.
The 2 Worksheets(i) lines needs a range object after them, I have used:-
• With Worksheets(i).Cells > where Cells in the context means the whole worksheet. Another good option would have been .UsedRange
• Worksheets(i).Rows > In the delete line since we wanted to delete entire rows.

In the If statement each expression (you have one before and one after the OR) has to be complete in itself
ie your line was If Not TopRow Or BottomRow Is Nothing Then 'Error Object variable or With block variable not set
The expressions are:
• Not TopRow is Nothing
• Not BottomRow is Nothing
Also you need them both to be not Nothing for your code to work so you then join them with AND