"Runtime error 1004" when For Next loop finishes (maybe because it hits the end of the excel file)?

[SnyperBunny]

My code is supposed to open a text file (it does this fine and I havent included that code in this snippet) and then search through it for a number of things - If column is something other than "New" it should delete the entire row. Also, if column E is anything other than a value on the list I have put in the code, it should delete the entire row as well.

Now... It does both of these things just fine (deleting the rows when the correct value is not found) The problem is that when it is finished it throws an error... and I would much rather it not throw an error. Its unsightly as well as stopping any further programming from running.

The error it throws is:

Runtime error '1004':
Application-defined or object-defined error​

With the highlighted-debug line bolded (and yellow) in the code below. It doesn't seems to pick any specific line though, I've been playing with it and its not picky with what it is highlighting, just that it is usually the line after the beginning of the For-Next statement.

My question is: Can I stop this error? If so, how? Whats wrong with my code? and if not, then how can I catch this error and continue on? (I would prefer to just stop the error from happening to begin with...)

The row 1 in my imported file has headers... I was thinking of checking column C for the header ("Vintage") and then telling the For Next Statement to break... perhaps that would stop it from bashing its 'head' into the top of the excel sheet and giving the error (this is my current theory)? However I dont know how to do that (I'm a fair VBA noob).

My other theory is that it is a glitch with this line:

For i = ActiveSheet.Range("C65500").End(xlDown).Row To C2 Step -1

I do not know what it is doing other than that I grabbed it from the internet, repurposed it and it seems to work. It only seems to work going from the very bottom of the spreadsheet to the top however. Top to bottom does nothing, and giving it a range of even 'Range("C500")' still modifies the file from the bottom (about 9000) to the top (row 1). This means that it ignores that it is only supposed to go to C2....

I'm so confused


edit: forgot to mention that I'm using Windows 7 and Microsoft office 2007.

Application.ScreenUpdating = False
   [COLOR=#006400]'Do While ActiveSheet.Cells(i, 3) <> "Vintage"
[/COLOR]   For i = ActiveSheet.Range("C65500").End(xlDown).Row To C2 Step -1
        
[COLOR=#006400]     'if cell is empty, does nothing, if something is in cell, checks if row should be deleted
[/COLOR][B][COLOR=#daa520]       If IsEmpty(ActiveSheet.Cells(i, 3)) = False Then
[/COLOR][/B]        
          [COLOR=#006400] 'checks if value of column C is anything other than "New" and then deletes the rows if it is.
[/COLOR]           If ActiveSheet.Cells(i, 3) <> "New" Then        
                ActiveSheet.Rows(i).Delete
           End If
            
[COLOR=#006400]           'checks if values in column E are not one of the values wanted - if so, row is deleted.
[/COLOR]           If ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Wind" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Bio_mass" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Pumped Storage" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Small_Hydro" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Large_Hydro" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "SCGT" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "CCGT" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Other" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "Rev6" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "SiteC" And ActiveSheet.Cells(i, 3).Offset(0, 2).Value <> "SMI" Then
               ActiveSheet.Rows(i).Delete
           End If
        
        End If
        
    Next
   [COLOR=#006400]'Loop
[/COLOR]  Application.ScreenUpdating = True

 

[JLGWhiz]

This line:

Code:
For i = ActiveSheet.Range("C65500").End(xlDown).Row To C2 Step -1
Code:

Would probably work better as:

Code:
For i = ActiveSheet.Range("C65500").End(xlUp).Row To C2 Step -1
Code:

The second version starts the range at the last row of data whereas the first version started the range at the absolute bottom of the column.

A lot of people mistakenly try to use the IsEmpty function to identify blank cells. It produces an error every time. IsEmpty only applies to a variable to determine if it has been initialized or not. Empty is a value assigned to the variable if it is not initialized with a different value.
This line:

Code:
If IsEmpty(ActiveSheet.Cells(i, 3)) = False Then
Code:

Probably should be:

Code:
If ActiveSheet.Cells(i, 3) <> "" Then
Code:

 

[SnyperBunny]

Thank you so so much for the quick reply :D
I'll try it now and see if it works.

 

[SnyperBunny]

It still worked to do what it was supposed to do... But also still gave the same error, and highlighted the same line which has been changed to:

If ActiveSheet.Cells(i, 3) <> "" Then

As far as going from the absolute bottom of the column, that is what I want to do eventually because this needs to work for all files imported in to it, and I dont know how many rows future files will have - this is why I have the 'if empty' statement in there - so it skips checking for row deletion conditions if the row is empty (my theory is that this sames time processing... and it does seem to).

I feel like the error might be because it is going from the bottom of the file to the absolute top of the spreadsheet, that it finishes checking the last row (row 1), and goes to check the next row up but fails because it doesnt exist. What I cannot fathom is why it would try to do that instead of just breaking out of the For-Next loop when it reaches cell C2 (which is what it should be going to). It definately goes until at least C1 because it deletes my first row (which as a header row does not contain any of the desired values it is looking for and so is deleted).

 

[SnyperBunny]

Ah! I managed to fix it!
In this line here:

For i = ActiveSheet.Range("C65500").End(xlDown).Row To C2 Step -1

it needed to be "To 2 Step" instead of "To C2 Step". I suppose it is my fault for using random code from the internet without totally understanding it... but it is a truly annoying syntax error...

 

[JLGWhiz]

Ah! I managed to fix it!
In this line here:

For i = ActiveSheet.Range("C65500").End(xlDown).Row To C2 Step -1

it needed to be "To 2 Step" instead of "To C2 Step". I suppose it is my fault for using random code from the internet without totally understanding it... but it is a truly annoying syntax error...

Glad you found it, I was just going to post this. You can read it anyhow, since it is already done.

If you are using xl2003 or earlier version then it only has 65,536 rows available per worksheet. So the C65500 effectively takes it to the bottom. Then the End(xlUp) is the same as a manual End + Up Arrow and it stops on the first cell it finds with a formual or data. So as the amount of data increases, your range automatically increases when that line of code is executed. It saves a little bit of time in code execution to do it that way.
That aside, I believe I have found the culprit and don't know why I missed it the first time. Change this line:

Code:
For i = ActiveSheet.Range("C65500").End(xlDown).Row To C2 Step -1
Code:

To this:

Code:
For i = ActiveSheet.Range("C65500").End(xlDown).Row To 2 Step -1
Code:

"i" should be declared as either Integer or Long in your Dim statements (Personally, I prefer Long). Both are integers but Long has more capacity and will avoid many stack overflow error messages. If you would hover your mouse pointer over the i in your code when it errors and you click the debug button, you would probably see that the valuie of "i" is "Empty". When you make the change, it should then show an integer value and the code should run.