SnyperBunny
New Member
- Joined
- Jan 21, 2013
- Messages
- 23
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:
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:
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.
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
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:
Code:
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.
Code:
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
Last edited: