VBA Error - run time error 1004 - application or object defined error

[Sam0803]

I have written some code to check test the case for cell value > 0.0007 and if it is true to delete that row and the 199 rows below. This works when the cell value is greater than 0.51, however when the cell value is between 0.0007 and 0.5 I get a runtime error.
This is my code



Sub TimeFilter()
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Dim Time As Single
Application.StatusBar = "Performing Time Filter..."
Range("F3893").Select ' select active cell - time difference
Do
Time = ActiveCell.Value
Select Case Time
Case Is > 0.01 ' test active cell for time filter
Range(ActiveCell, Cells(ActiveCell.Row + 119, ActiveCell.Columns)).Delete ' if condition is met delete active cell and 120 rows below ***ERROR***
Selection.Cells(1, 1).Activate

Case Else
ActiveCell.Offset(1, 0).Select ' moves to next row if condition above is not met
End Select
Loop Until ActiveCell.Value = " "
Application.StatusBar = False
Application.ScreenUpdating = True


End Sub

Any help is much appreciated

Thanks
Sam

 

[JLGWhiz]

Cells(ActiveCell.Row + 119, ActiveCell.Columns)).

Remove the s from Columns.

 

[Sam0803]

Remove the s from Columns.

That works Thanks!

I'm getting another run time error now on this row

ActiveCell.Offset(1, 0).Select ' moves to next row if condition above is not met

Any ideas?

 

[JLGWhiz]

I don't get an error on that line, but I question this line:

Loop Until ActiveCell.Value = " "

If you intend for that AactiveCell.Value = (space) then there is no problem, but if you intend it to = (blank), then you need to remove the space between the quote marks. Blank or Empty String is represented by (""), two quotation marks with no space between.
Sometimes, the offset function will throw an error because the active cell has reached the end of a column or row and cannot offset past the borders of the sheet. Since you have the space instead of the Empty String value in the Loop Until statement, I suspect it reached the bottom of the page and then threw the error. Changing the " " to "" should fix that problem.

 

[Sam0803]

I don't get an error on that line, but I question this line:

Loop Until ActiveCell.Value = " "

If you intend for that AactiveCell.Value = (space) then there is no problem, but if you intend it to = (blank), then you need to remove the space between the quote marks. Blank or Empty String is represented by (""), two quotation marks with no space between.
Sometimes, the offset function will throw an error because the active cell has reached the end of a column or row and cannot offset past the borders of the sheet. Since you have the space instead of the Empty String value in the Loop Until statement, I suspect it reached the bottom of the page and then threw the error. Changing the " " to "" should fix that problem.

Thanks, It was supposed to be blank, and it seems to have fixed it!

 

[JLGWhiz]

Thanks, It was supposed to be blank, and it seems to have fixed it!

Happy to help,
Regards, JLG