Solved: deleting rows beneath active cell one by one till value is found in column

[sunilmulay]

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

[VBA]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[/VBA]

[senthil.sak7]

can u attach the file for better understanding.

[sunilmulay]

hi there

please see attached simplified file for a better idea.....

thanks
Sunil

[Bob Phillips]

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]

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]

can anyone help with this please?????
Thanks in advance
Sunil

[GTO]

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]

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]

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]

[vba]

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
[/vba]

[Bob Phillips]

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]

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.

[VBA]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[/VBA]

[Bob Phillips]

Not tested, but try this

[vba]

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
[/vba]

[sunilmulay]

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]

Can you post a workbook that we can work on?

[sunilmulay]

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]

....I hope my explanation was clear enough....
please let me know if you need a better explanation!
Thanks
SM