Yes, it did delete the highlighted rows very nicely. However, I copied your
20 rows down through the end of the file, ran the macro and the only thing
deleted was column D. Why would that be?

I did correct one thing which was giving a #REF - the problem technically
shouldn't exist but when data is entered by hand it isn't always the same as
that entered via barcode scanner. Had to browse through my 26000 lines to
figure that out. The mismatch error is now GONE!

However, it appears that the code is running over and over again, as the
screen refreshes indefinitely. There's no way that I know of to see if it
has actually deleted what it was supposed to delete.

This code is just going into a regular macro, not a module or ws code or any
others that I'm not familiar with.

Thanks, as always, for your help.

Ryan

"Sheeloo" wrote:

Download the file from http://wikisend.com/download/962090/Test1.xls

When you run the macro it will delete the highlighted rows...

Where are adding the code? In a module?

"seed" wrote:

The same line is still giving the error.

"Sheeloo" wrote:

I think I had given the solution... anyway here it is

Use the following code;
Sub deleteOBS()
obs = InputBox("Pl enter a number")

'MsgBox "Obs value is = " & obs
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i, "D") < obs * 2 Then Cells(i, "D").EntireRow.Delete
Next i
Range("D1:D65536").Select
Selection.Delete

End Sub

If you enter 5 it will remove all rows which don't have 10 in Col D and then
clear Col D

==========================
This is what I had given earlier
'Sub 1
Sub deleteotherobsnumber(obs As Double)
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i, "d") < obs * 2 Then Cells(i, "d").EntireRow.Delete
Next i
Range("D1:D65536").Select
Selection.Delete
End Sub

'Sub to call the above sub
Sub test()
noToDelete = 5
deleteotherobsnumber (noToDelete)
End Sub

"seed" wrote:

Posted this two days ago but fear it has fallen by the wayside once off the
front page...

obs is taken from an inputbox and the msgbox returns the correct value.
Point of this is to go through the sheet row by row and delete any row where
column D does not equal twice the value of obs. I'm not completely familiar
with VBA and may well have screwed something up. "If cells (i,"D")..." line
is giving the mismatch error.

All help is much appreciated.


MsgBox "Obs value is = " & obs
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i, "D") < obs * 2 Then Cells(i, "D").EntireRow.Delete
Next i
Range("D1:D65536").Select
Selection.Delete