Solved: Deleting rows as if by magic!!

[maxhayden]

Hi there,

I have some code that works (finally).

Eg/
1.00
1.01
1.02
2.00
2.01
2.02
3.00
3.01
3.02

If I put 2.00 into a textbox, it will delete 2.00, 2.01 and 2.02. This is good.

What is bad is that when I close the userform, it deletes 1.02?????

Private Sub CommandButton1_Click()

For j = 100 To 7 Step -1
y = Task_Deleter.TextBox1.Value

If WorksheetFunction.RoundDown(Cells(j, 2), 0) = y * 1 Then
Rows(j).Delete

End If
Next j

'Re-Sort Main Task numbers
For i = 8 To 100

If Not WorksheetFunction.RoundDown(Cells(i, 2), 0) = Cells(i, 2).Value Then

Cells(i, 2).Value = Cells(i - 1, 2).Value + 0.01
Else

If Cells(i, 2) <> "" Then

Cells(i, 2).Value = WorksheetFunction.RoundDown(Cells(i - 1, 2), 0) + 1

End If
End If

Next i

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub Label1_Click()
End Sub


I have never seen this before and I can't see why it would be doing it. It does it if I manually close the userform (with the x) or if I use Unload Me.

:(

[mdmackillop]

You have deleted your workbooks from your previous post. These form an integral part of the solution which is posted for the benefit of all.
Please do not delete posts or attachments.

[maxhayden]

Apologies,

I am new to this so wasn't sure what the procedure was (and I'm slightly embarassed about my child-like programming!)

Please link this to my previous thread so others can use it.

(Attached)

You will need to use the "Add task" button to add a few Main and Sub tasks to test it out.

The issue I'm refering to is in the "Edit Tasks" button, then the "Task Deleter" button --> It works fine for deleting subtasks (x.01, x.02, etc..) but when you delete a main task (x.00) it works... but then does the crazy thing mentioned above

Rgds,

max

[Kenneth Hobs]

Your code example works as expected and did not reproduce the problem. I will have to look at your attachment to see what else is going on.

Option Explicit

Private Sub CommandButton1_Click()
Dim y As Double, i As Integer, j As Integer

y = Task_Deleter.TextBox1.Value * 1
For j = 100 To 7 Step -1
If WorksheetFunction.RoundDown(Cells(j, 2), 0) = y Then Rows(j).Delete
Next j

Exit Sub
'Re-Sort Main Task numbers
For i = 8 To 100
If Not WorksheetFunction.RoundDown(Cells(i, 2), 0) = Cells(i, 2).Value Then
Cells(i, 2).Value = Cells(i - 1, 2).Value + 0.01
Else
If Cells(i, 2) <> "" Then
Cells(i, 2).Value = WorksheetFunction.RoundDown(Cells(i - 1, 2), 0) + 1
End If
End If
Next i
End Sub

[maxhayden]

I have tried closing it and re-starting it and it still does the same thing.

When I (x) the Userform it deletes the last subtask under Main Task 1.00 regardless of what main task number I am deleting

[Kenneth Hobs]

Check to be sure that you are running the code like what you posted.

Your attachment had code where it used Find for a whole row. So, any row could have a 2 in some part so that row would be found.

[mdmackillop]

Private Sub CommandButton1_Click()
'Main Task Hunt
x = Task_Deleter.TextBox1.Value
If Fix(x) - x = 0 Then
MainTask_Warning.Show
Exit Sub
End If

[maxhayden]

The "Find" code is only used for the sub task deleter.. this is because it only deletes 1 line

If you look at the code for Main Task deleting which is under the MainTask_Warning button.

The Main Task deleter loops becasue it deletes all lines with x in the front

[maxhayden]

That works!!

I had exit sub in there before but took it out when I was trying to fix the previous problem!! I must have forgotten about it.

Thanks very much!

Rgds,

Max

[mdmackillop]

To avoid unneccessary looping, you can determine the last row to be processed.


Private Sub CommandButton1_Click()
'Main Task Hunt
x = Task_Deleter.TextBox1.Value
If Fix(x) - x = 0 Then
MainTask_Warning.Show
Exit Sub
End If
'Subtask Hunt
y = Task_Deleter.TextBox1.Value
Worksheets("Plan Overview").Columns(2).Find(y * 1).EntireRow.Delete
'Re-Sort Subtask numbers
For h = 8 To LR(2) 'or LR("B")
If Not Int(Cells(h, 2).Value) = Cells(h, 2).Value Then
Cells(h, 2).Value = Cells(h - 1, 2).Value + 0.01
End If
Next h
End Sub

Function LR(Col) As Long
LR = Cells(Rows.Count, Col).End(xlUp).Row
End Function



also, if your code is complete when an action occurs, Exit the loop at that point.


Private Sub Test()
For h = 8 To LR(2)
If Cells(h, 2).Value = 10 Then
'do something
Exit For
End If
Next h
End Sub

[Kenneth Hobs]

Please explain how that routine is ran. What macro does this? You might post a file with data filled in so we can test after you explain in more detail. As I said, the routine is fine. Something else is causing your problem. I need to follow your steps to trace it back.

[mdmackillop]

Hi Kenneth,
There are two delete routines. The Sub deletes one row (intended data 2.03 or the like), the Main (data 1 or 2 etc.) deletes all rows starting with selected number. The code was running Main, then running Sub, causing an inadvertent deletion.