-
Solved: Deleting rows as if by magic!!
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?????
[vba]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
[/vba]
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.
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
Last edited by maxhayden; 05-29-2009 at 08:35 AM.
-
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.
[VBA]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[/VBA]
-
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
-
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.
-
[VBA]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[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
-
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
-
To avoid unneccessary looping, you can determine the last row to be processed.
[VBA]
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
[/VBA]
also, if your code is complete when an action occurs, Exit the loop at that point.
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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.
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules