Code to delete rows giving mismatch error

S

seed

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
 
S

Sheeloo

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
 
S

seed

Wow. I think I saw " --------------------------------------------------"
separating the two halves of your message and unconsciously assumed that the
rest was my quoted text. Didn't read it!

I'll give this a try. Thanks.
 
S

seed

The same line is still giving the error.

Sheeloo said:
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
Click to expand...
 
S

Sheeloo

Test with
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

What error do you get?
 
S

Shane Devenshire

Hi,

If you are checking for exactly twice the input value then:

Sub DeleteRows()
Dim x As Double
On Error Resume Next
x = InputBox("What number?")
If IsNumeric(x) Then
Columns("E:E").Insert
Range("E1:E" & Range("E65536").End(xlUp).Row).Select
Selection = "=1/(2*" & x & "<>RC[-1])"
Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns("E:E").Delete
End If
End Sub

This code assumes the the column with data is D and that there are no text
entries such as title, if you have a title at the top you can set the range
E1 to start at the row below any titles. If you have text intersperced with
the numbers you will need to modify the code a little. This macro runs about
50-100 times faster than using a loop.
 
G

Gord Dibben

Dim Obs As Long
Obs = InputBox("Enter a value")
MsgBox "Obs value is = " & Obs
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i, "D").Value <> Obs * 2 Then Cells(i, "D").EntireRow.Delete
Next i


Gord Dibben MS Excel MVP
 
S

seed

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
 
Top