Object variable or With block variable not set

Hello!

I'm getting this error, "Object variable or With block variable not set" using this macro:


FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To 2000
BeginRow = 1
EndRow = 2000
ChkCol = 2

Cells.Find(What:="TOTAL").Select
StartRange = ActiveCell.Address
Selection.Offset(3, 0).Select
EndRange = ActiveCell.Address
ActiveSheet.Range(StartRange & ":" & EndRange).Select
Selection.EntireRow.Delete
Next x



How do I set the variable or otherwise overcome this? The macro is working, it's just not seeming to come to an error-free end. I want the macro to find each "total" cell and then hide a few rows. The sheet size will vary, so I set an arbitrary number of times to repeat, knowing it won't be more than 2000. But it seems like it's not finding an ending and I don't know how to fix that.

Thank you!

Please use Code Tags (see below).

What are you trying to do? If you're trying to delete an entire row, or rows, there's not much point in determining a start and end range just so you can delete three rows.

How many rows have the word TOTAL in them? If it's less than 2000, that's why you get the error. What is the purpose of BeginRow, EndRow and ChkCol? You don't use them.

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

It fails when the find doesn't return a range as an object. You need to handle that.

Please Login or Register  to view this content.

It is not clear for what cycle and other variables

Replace part of code

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

Note: the two other solutions offered will both work. However, you will go through the loop 2000 times regardless of how many rows you find to delete. So, potentially, up to 1999 more loops than you need.

combining the two.

Please Login or Register  to view this content.

You're welcome.



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


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

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


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.