| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting a range of rows based on a variable; syntax error | Excel Discussion (Misc queries) | |||
| Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
| Wrong syntax | Excel Discussion (Misc queries) | |||
| wrong syntax | Excel Discussion (Misc queries) | |||
Hybrid Mode
