Delete Entire Row upon deleting a cel

[RobertBon]

Hello,

I'm trying to delete an entire row upon deletion of the contents in a cell:

If Not Intersect(target, Range("B:B")) Is Nothing Then

Set target = Intersect(target, Range("B:B"))
'If target Is Nothing Then Exit Sub

If target.count > 1 Then Exit Sub

For Each rngcell In target
If rngcell.Value = "" Then
'holds value
temp = target.Offset(1, -1).Value
target.Offset(0, -1).ClearContents

'delete active row
Rows(ActiveCell.Row).Select
Selection.Delete

'replace value with previously stored value
ActiveCell.Value = temp

'counter = counter - 1
'Range("f1") = counter
Else
target.Offset(0, -1).Value = counter
counter = counter + 1
Range("f1") = counter 'checkingvalue of counter
End If
Next

One of the issues I'm getting though is that if I delete the last value in a list Excel seems to get caught in a bit of a loop.

Any suggestions on how to fix this?

 

[gsbelbin]

Is this code from a Worksheet_Change procedure? If so the problem may be caused by the procedure being called each time you make changes to the sheet in the procedure itself, if you see what I mean. You may need to disable events each time you make a change to the sheet such as updating cells or deleting rows.

Application.EnableEvents = False

Then re-enable events once you've made the change.

Application.EnableEvents = True

 

[AlphaFrog]

I'm not sure I follow every thing you're doing, but I have this question; Why do you have a For Each -Next loop?

You have this line...
If target.count > 1 Then Exit Sub
Which means the rest of the code will only run if Target is one cell.

Then you have your For Each - Next loop.
For Each rngcell In target
...but there can only be one cell in target as determined previously. So there is nothing to loop.

If the loop is causing your infinite loop problem, and you don't need the loop in the first place, it seems the solution is to just get rid of the problem.

 

[RobertBon]

Thank you for the replies,

AlphaFrog - I see what you mean, the for loop was kind of redundant but when I got rid of, I still have the same problem as before.

gsbelbin - It is a worksheet_change procedure. I thought that was the problem but where would you make it true and false?

 

[gsbelbin]

You could just make it false at the start of the procedure and true at the end. You might want to have an error handler which sets it to true in case the procedure fails before it gets to the line which makes it true.

 

[RobertBon]

Okay. So now my code just decided to crash and is not working at all at the moment...

Let me try to reiterate what I'm trying to do.
Let's say I have a chart as so:
<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">Id </td> <td class="xl24" style="border-left:none;width:48pt" width="64">Name</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">0</td> <td class="xl24" style="border-top:none;border-left:none">Rob</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">1</td> <td class="xl24" style="border-top:none;border-left:none">Bob</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">2</td> <td class="xl24" style="border-top:none;border-left:none">Luc</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">3</td> <td class="xl24" style="border-top:none;border-left:none">Joe</td> </tr> </tbody></table>
I want to be able to add another entry to the "name column" and have an id number populate based on what was before it (so the list is in order)
At the same time, if a name is deleted, I'd like the names list to shift up and the Id number still be in correct order.

This is the code that decided not to work all of a sudden:

Private Sub Worksheet_Change(ByVal target As Range)
' Code goes in the Worksheet specific module

Dim counter As Integer
Dim temp As Integer

counter = Range("F1").Value 'starts at 0

If Not Intersect(target, Range("B:B")) Is Nothing Then

Set target = Intersect(target, Range("B:B"))
'If target Is Nothing Then Exit Sub

If target.count > 1 Then Exit Sub

If target.Value = "" Then

'holds value
temp = target.Offset(0, -1).Value
target.Offset(0, -1).ClearContents


'delete active row
Rows(ActiveCell.Row).Select
Selection.Delete

'replace value with previously stored value
ActiveCell.Value = temp

Else
target.Offset(0, -1).Value = counter
counter = counter + 1
Range("f1") = counter 'checkingvalue of counter
End If

End If

End Sub

This is my problem

 

[AlphaFrog]

If you want column A to always be numbered sequentially e.g.; 0,1,2,3...
You could use this formula

Put this in cell A2
=IF(B2="","",ROW()-2)
...And drag the formula down. Drag it well past your last entry.

Then if you delete a row, the formula will re-sequence the numbers. If you add a name, the formula will add the next number in the sequence.

This ROW()-2 is the row number the function is in minus two.

This code will delete the entire row if you clear a name from column B.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Code goes in the Worksheet specific module
   
    If Target.Count = 1 And Target.Column = 2 And Target.Value = "" Then
        Application.EnableEvents = False    'Prevents triggering another Worksheet_Change event
        Target.EntireRow.Delete             'Delete row if name was cleared
        Application.EnableEvents = True     're-enable event triggers
    End If

End Sub

 

[RobertBon]

Thanks alphafrog the Application Enable Events is making it run well now

Cheers!