View Full Version : Solved: deleting rows beneath active cell one by one till value is found in column
sunilmulay
10-11-2008, 10:10 PM
Hi There
Please see the following code. I want the macro to delete 5 rows if the user clicks on a sub task (this part works). The tricky part is if a user clicks on a main task, the macro should delete rows one by one beneath it till it finds a main task (where the value in Column B=1), and then STOP. Currently, it finds ALL rows in the spreadsheet where Column B=1 and deletes them!
Thanks!
S
Sub DeleteTask01()
'
'
Dim SelectedRow As Long
SelectedRow = ActiveCell.row
If Cells(SelectedRow, 2).Value <> 1 Then
Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete
Else
Dim i As Long
For i = SelectedRow To 999
If Cells(i, 2).Value <> 1 Then
Cells(i,2).EntireRow.Delete
End If
Next i
End If
End Sub
senthil.sak7
10-12-2008, 12:29 AM
can u attach the file for better understanding.
sunilmulay
10-12-2008, 12:57 AM
hi there
please see attached simplified file for a better idea.....
thanks
Sunil
Bob Phillips
10-12-2008, 04:31 AM
That workbook has no code that looks remotely like the code that you posted, and calls a procedure that doesn't exist. Not much for us to work with.
sunilmulay
10-12-2008, 05:36 AM
Hi there
Sorry - I deleted pretty much everything to simply the file!
I've removed unnecessary sheets now, but left most of the code....
Thanks
S
sunilmulay
10-13-2008, 02:42 AM
can anyone help with this please?????
Thanks in advance
Sunil
Sunil -
By example, if I were to select cell D22, and pressed the <Delete Task> button, do you want it to delete rows 22 thru (inclusive) 35?
Bob Phillips
10-13-2008, 03:13 AM
I don't think that you are doing much to help us.
The macro behind the delete button doesn't exist.
The sheets are protected.
I can't believe that the tasks are eally called Main Task, Sub Task, so how are they identified.
And so on.
You have got to do your bit or we will walk!
sunilmulay
10-13-2008, 04:05 AM
Hi
I'm really sorry about that - it's not intentional! I'm trying to post a simplified file so it's easy to understand my challenge, and I'm not used to dealing with all these macros/passwords, etc.
hopefully the attached file will work. I've unprotected the sheets.
The situation will be that the sheets will be protected and all the gray rows hidden. The user will create Main/Sub Tasks as required using other Macros not shown. What I need is if the user clicks on any main task (you'll note they have a "1" in column B), then the macro should delete rows below it till it finds another Main task with a "1" in Column B and then stop.
Thanks (and sorry for the hassle)
Sunil
Bob Phillips
10-13-2008, 04:43 AM
Sub DeleteTask01()
Dim SelectedRow As Long
Dim i As Long
SelectedRow = ActiveCell.Row
If SelectedRow > 20 Then
If Cells(SelectedRow, 2).Value <> 1 Then
Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete
Else
i = SelectedRow + 1
Do Until Cells(i, 2).Value = 1
i = i + 1
Loop
Rows(SelectedRow).Resize(i - SelectedRow).Delete
End If
End If
End Sub
Bob Phillips
10-13-2008, 04:44 AM
Just a thought. If you try to delete the last main task, it will fail. You need some sort of marking identification so that the code can test for the end of the data as well as the next main task.
sunilmulay
10-13-2008, 05:44 AM
thanks so much for the code (and your patience). That worked really well. And I added a blank line with the number 1 in Column B to provide the "marker".
One last thing on this macro: i'm trying to extend the macro so it performs the exact same function on the next Sheet. (Sheet B). I did a similar thing for another Macro to Insert rows and that worked well, but when I run the following code, it deletes pretty much all the rows below.... What am I doing wrong?
Thanks again - Sunil.
Sub DeleteTask01temp()
Dim SelectedRow As Long
Dim i As Long
Dim SheetB As Worksheet
Set SheetB = Sheets("Stage01-Tracking")
SelectedRow = ActiveCell.row
If SelectedRow > 20 Then
If Cells(SelectedRow, 2).Value <> 1 Then
Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete
With SheetB
Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete
End With
Else
i = SelectedRow + 1
Do Until Cells(i, 2).Value = 1
i = i + 1
Loop
Rows(SelectedRow).Resize(i - SelectedRow).Delete
With SheetB
i = SelectedRow + 1
Do Until Cells(i, 2).Value = 1
i = i + 1
Loop
Rows(SelectedRow).Resize(i - SelectedRow).Delete
End With
End If
End If
Set SheetB = Nothing
End Sub
Bob Phillips
10-13-2008, 06:06 AM
Not tested, but try this
Sub DeleteTask01temp()
Dim SelectedRow As Long
Dim i As Long
Dim SheetB As Worksheet
Set SheetB = Sheets("Stage01-Tracking")
SelectedRow = ActiveCell.Row
If SelectedRow > 20 Then
If Cells(SelectedRow, 2).Value <> 1 Then
Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete
SheetB.Range("B" & SelectedRow & ":B" & SelectedRow + 5).EntireRow.Delete
Else
i = SelectedRow + 1
Do Until Cells(i, 2).Value = 1
i = i + 1
Loop
Rows(SelectedRow).Resize(i - SelectedRow).Delete
With SheetB
i = SelectedRow + 1
Do Until .Cells(i, 2).Value = 1
i = i + 1
Loop
.Rows(SelectedRow).Resize(i - SelectedRow).Delete
End With
End If
End If
Set SheetB = Nothing
End Sub
sunilmulay
10-13-2008, 02:15 PM
that works for sub tasks, but fails when run on a main task. It fails on the line: [Do Until .Cells(i, 2).Value = 1] under With SheetB Section...
I'll try and play around with it when I'm home this evening, unless you have any ideas.....
Thanks
S
Bob Phillips
10-13-2008, 02:25 PM
Can you post a workbook that we can work on?
sunilmulay
10-14-2008, 01:58 AM
Hi
Attached is the workbook. The first sheet is the one with the buttons to Insert and Delete rows which should execute the same command on the other two sheets.
In addition to the above query, I also have a query regarding the Insert Task macro. You'll see the rows it copies to insert are deliberately hidden. How do I get the macro to unhide the first of the rows that it inserts?
Thanks a bunch.
S
sunilmulay
10-14-2008, 05:42 AM
....I hope my explanation was clear enough....
please let me know if you need a better explanation!
Thanks
SM
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.