View Full Version : deleting rows based on content of rows below
sunilmulay
10-07-2008, 02:37 AM
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
10-07-2008, 02:41 AM
Why not just filter that column for all rows where the value is <> 1 and delete them?
sunilmulay
10-07-2008, 02:57 AM
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
10-08-2008, 01:15 AM
any ideas guys????
Thanks
S
georgiboy
10-08-2008, 01:35 AM
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
10-08-2008, 03:13 AM
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
10-08-2008, 03:41 AM
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
10-08-2008, 03:47 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.