deleting rows based on content of rows below

[sunilmulay]

Hi there
Maxim on this forum offered the following code to delete rows depending on the cell the user clicks on. I want to now change the first condition, so that "If Cells(SelectedRow, 2).Value = 1", the Macro deletes all rows beneath it till it finds a row where "Cells(SelectedRow, 2).Value = 1". But before performing the action I want to pop the message "Deleting this task will delete all sub tasks. Is this ok?" and give the user the option of saving Ok/Cancel to proceed with or cancel the task...
hope this is not too complicated.....
Thanks
Sunil

Sub DeleteRowPlanning()
ActiveSheet.Unprotect Password:=PWORD
Dim SelectedRow As Long

SelectedRow = ActiveCell.row

If Cells(SelectedRow, 2).Value = 1 Then

Range("B" & SelectedRow & ":B" & SelectedRow + 1).EntireRow.Delete

Else

Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete

End If
ActiveSheet.Protect Password:=PWORD

End Sub

[Bob Phillips]

Why not just filter that column for all rows where the value is <> 1 and delete them?

[sunilmulay]

That's not what I want to do. I have a list of tasks, some are Level 1 and other are Level 2 tasks (sub-tasks). Imagine a list like this:

1 Main task 1
1.1 Sub task 1
1.2 Sub Task 2
1.3 Sub Task 3
2 Main task 2
2.1 Sub Task 4
2.2 Sub Task 5
3 Main Task 3
3.1 Sub Task 6

It the user clicks on Main Task 2, i want the macro to delete items 2, 2.1 and 2.2 only, subject to the pop up box I mentioned before.

I am a VBA newbie, so I am struggling with this (probably very simple) answer.

Thanks
Sunil

[sunilmulay]

any ideas guys????
Thanks
S

[georgiboy]

You could try this, it needs to go into the worksheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rCell As Range, MyRange As Range, DelCri As String

YesNo = MsgBox("This macro will Delete all subtasks Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes

Set MyRange = Sheet1.Range("A1:A500")

DelCri = Left(ActiveCell.Value, 1)

For Each rCell In MyRange.Cells

If Left(rCell.Value, 1) = DelCri Then rCell.ClearContents

Next rCell

Case vbNo

Exit Sub

End Select
End Sub

Hope this helps

[sunilmulay]

Hi there
I haven't tried your suggestion, but to my limited VBA acumen (!!), I don't think that would solve my problem...because it would delete all contents with that condition throughout the range, right?
let me illustrate the situation better. Following is what the data looks like (simplified):

A B C D
1 MainTask1 Blah Blah Blah
. 1.1 Sub Task1.1 Blah blah blah
2 Main task 2 dkfjsklfjdskjfsd
. 2.1 Sub Task 2.1 fskdjfldskfjdsf
. 2.2 Sub Task 2.2 dfksjdfklsjfsdf
3. Main Task 3 dfsdkfjdsfjsdf
. 3.1 Sub Task 3.1 dfkljsdkfjdskjf
. 3.3 Sub Task 3.2 lfjdsjfkldsjfklsdjf

I want to create a button that runs a macro to delete rows. If the users clicks a main item, I want the macro (after the user accepts yes) to delete the main task and all its sub tasks. So in VBA terms, the script should delete the EntireRow of the ActiveCell and then continue deleting +1 rows until if finds a row which has a number in Column A, and then stop.

hope this is clearer....??

Thanks
Sunil

[georgiboy]

Try This, attatch it to a button

Sub DelSubTasks()

YesNo = MsgBox("This macro will Delete all subtasks Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes


ActiveCell.EntireRow.ClearContents

Do

ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.ClearContents

Loop While IsNumeric(ActiveCell.Value) = False


Case vbNo

Exit Sub

End Select

End Sub

Hope this helps

[Krishna Kumar]

Hi,

Sub kTest()
Dim TaskNo As Long, TaskRow As Long, EndRow As Long, Ans
TaskNo = Application.InputBox("Enter task no", "Delete task", 1, Type:=2)
If IsNumeric(TaskNo) Then
With ActiveSheet.Columns(1)
On Error Resume Next
TaskRow = .Find(what:=TaskNo, LookIn:=xlWhole).Row
On Error GoTo 0
If TaskRow > 0 Then
EndRow = .Find(what:="*", after:=.Cells(TaskRow, 1)).Row - 1
If EndRow = Rows.Count Then EndRow = ActiveSheet.UsedRange.Rows.Count
Else
MsgBox "Task No " & TaskNo & " not found", vbInformation
Exit Sub
End If
End With
Ans = MsgBox("Are you ready to delete rows " & _
TaskRow & ":" & EndRow & " ?", vbYesNo)
If Ans = vbYes Then
Rows(TaskRow & ":" & EndRow).EntireRow.Delete
End If
End If
End Sub

HTH