Deleting rows - syntax wrong?
 

I borrowed this code from another post. Don't recall whose it was, but
thanks for it.

Anyway, I adapted it as well as I could to what I'm using it for and am
getting an error.

I've already set the dim obs as a value from a cell. I want this code to
delete all rows where column D does not equal 2*the value of obs. This may
be a really simple question.




Sub deleteotherobsnumber()
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

Deleting rows - syntax wrong?
 

What error? Type mismatch or ... Which line generates the error?
--
HTH...

Jim Thomlinson


"seed" wrote:

I borrowed this code from another post. Don't recall whose it was, but
thanks for it.

Anyway, I adapted it as well as I could to what I'm using it for and am
getting an error.

I've already set the dim obs as a value from a cell. I want this code to
delete all rows where column D does not equal 2*the value of obs. This may
be a really simple question.




Sub deleteotherobsnumber()
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

Deleting rows - syntax wrong?
 

How will the Sub get the value of Obs?

Is it a global variable...

Code is OK... It will work as long as it know the value of Obs

Add this after the "Sub deleteotherobsnumber()"
Msgbox "Obs value is = " & Obs

If you get a value then your code will work...
------------------------------------------------------------------
You should define your sub like this

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

'And call from another sub like this
Sub test()
noToDelete = 5
deleteotherobsnumber (noToDelete)
End Sub


"seed" wrote:

I borrowed this code from another post. Don't recall whose it was, but
thanks for it.

Anyway, I adapted it as well as I could to what I'm using it for and am
getting an error.

I've already set the dim obs as a value from a cell. I want this code to
delete all rows where column D does not equal 2*the value of obs. This may
be a really simple question.




Sub deleteotherobsnumber()
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

Deleting rows - syntax wrong?
 

I added the code directly into the macro calling the separate subroutine.
Still happening - it is a 'type 13' mismatch error

obs is taken from an inputbox, the msgbox returns the correct value

Sub cleanevaldata()
Static obs As Integer
On Error Resume Next
Application.DisplayAlerts = False
obs = Application.InputBox(Prompt:="Usual # of obs for this test:",
Title:="Obs?", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If obs = 0 Then
Exit Sub
Else
GoTo 10
End If
10 Rows("1:1").Select
Selection.Delete
Columns("C:C").Select

....

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
<----error here
Next i
Range("D1:D65536").Select
Selection.Delete







"Sheeloo" wrote:

How will the Sub get the value of Obs?

Is it a global variable...

Code is OK... It will work as long as it know the value of Obs

Add this after the "Sub deleteotherobsnumber()"
Msgbox "Obs value is = " & Obs

If you get a value then your code will work...
------------------------------------------------------------------
You should define your sub like this

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

'And call from another sub like this
Sub test()
noToDelete = 5
deleteotherobsnumber (noToDelete)
End Sub


"seed" wrote:

I borrowed this code from another post. Don't recall whose it was, but
thanks for it.

Anyway, I adapted it as well as I could to what I'm using it for and am
getting an error.

I've already set the dim obs as a value from a cell. I want this code to
delete all rows where column D does not equal 2*the value of obs. This may
be a really simple question.




Sub deleteotherobsnumber()
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

Deleting rows - syntax wrong?
 

I see that my note of where the error occurs skips down a line.

If Cells(i,"D")... gives the error.

Thanks both of you.



"seed" wrote:


I added the code directly into the macro calling the separate subroutine.
Still happening - it is a 'type 13' mismatch error

obs is taken from an inputbox, the msgbox returns the correct value

Sub cleanevaldata()
Static obs As Integer
On Error Resume Next
Application.DisplayAlerts = False
obs = Application.InputBox(Prompt:="Usual # of obs for this test:",
Title:="Obs?", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If obs = 0 Then
Exit Sub
Else
GoTo 10
End If
10 Rows("1:1").Select
Selection.Delete
Columns("C:C").Select

...

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
<----error here
Next i
Range("D1:D65536").Select
Selection.Delete







"Sheeloo" wrote:

How will the Sub get the value of Obs?

Is it a global variable...

Code is OK... It will work as long as it know the value of Obs

Add this after the "Sub deleteotherobsnumber()"
Msgbox "Obs value is = " & Obs

If you get a value then your code will work...
------------------------------------------------------------------
You should define your sub like this

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

'And call from another sub like this
Sub test()
noToDelete = 5
deleteotherobsnumber (noToDelete)
End Sub


"seed" wrote:

I borrowed this code from another post. Don't recall whose it was, but
thanks for it.

Anyway, I adapted it as well as I could to what I'm using it for and am
getting an error.

I've already set the dim obs as a value from a cell. I want this code to
delete all rows where column D does not equal 2*the value of obs. This may
be a really simple question.




Sub deleteotherobsnumber()
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