Change event macros, macro crashes when row deleted

[Legacy 96851]

Let's see how quickly the collective brain of the most spreadsheet-advanced forum on the internet can fix this. Actually, I figure it's just a basic command or if statement I'm missing, but I don't know how to phrase it.

This is my worksheet's change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AD:AF")) Is Nothing Then
        Call Summary.summarize
    End If
    If Not Intersect(Target, Sheets("ProjectData").UsedRange) Is Nothing Then
        edited = Target.Row
        SingleVal Sheets("ProjectData").Range(edited & ":" & edited)
    End If
End Sub

As you can see, if any cell in columns AD:AF is changed, both of these macros get called. In and of itself that's no problem, but whenever I delete a row, Excel locks up and doesn't come back. Deleting columns, even those three targeted, doesn't cause any problems. Is there a way to put a something like "if target was not deleted" in the code?

 

[Legacy 96851]

Thanks for the clarification, Jonmo. I did try that, and, unfortunately, it didn't work. I think for now I'm probably going to stick with your initial solution, because, as I said before, the circumstance in which it fails is incredibly unlikely, and I'm not exactly producing this for commercial use.

 

[Norie]

I'm sorry if you feel I was been sarcastic or whatever, I certainly didn't mean to be.

All I'm trying to point out is that for us to help you we need you to help us.

ie post the other code, because that's the most likely place the problem is happening.

And what's wrong with suggesting you use the debug features?



By the way it's good to hear that you are trying to help yourself by googling etc, lots of folk don't bother.

 

[Legacy 96851]

No hard feelings or anything, like I said, just letting you know where I'm coming from here. I'm very grateful for any replies.

I do try as much as I can to post source code, it's just not always practical. The problem is that posting my 3 page macro won't get me any more help than posting no code, and maybe just a slap on the wrist as well. I'd try to trim it down, but that takes time, and, like everyone else here, I have other things that need to get done. I try to get as much help as I can without posting it; oftentimes I can't tell how stupid the question is, so I don't want to waste time posting up code, just to be told "oh, Excel has a function that does that, you know."

 

[Norie]

I've got no problem with somebody posting lengthy code.

And I'm pretty sure if you did post it, or just even portions of it, we could give you pointers in how you could trim it back

 

[Legacy 96851]

Alright, just this once, since you asked so nicely (or was because you asked so many times?). I also should probably be more careful for legal reasons, but I'll let that go for the moment.

The problem is most likely caused by this macro, since the only columns that cause crashing are the ones on which this runs.

Sub SingleVal(edited As Range)
    'A version of the above method used to update single rows (namely for active updating)
 
    Dim check As Worksheet, list As Worksheet
 
    Set check = ActiveWorkbook.Sheets("ProjectData")
    Set list = ActiveWorkbook.Sheets("Validation Req'd")
 
    thing = edited.Row
    With list
        For x = 1 To .Range("A" & Rows.Count).End(xlUp).Row
            If .Range("A" & x) = edited(1, 3) Then
                targetrow = x
            End If
        Next x
        .Range("D" & targetrow).ClearContents
    End With
 
    With check
        For i = 1 To .Cells(1, 1).End(xlToRight).Column
 
            If .Cells(1, i) = "Audit CMMI" Then
                audCM = i
            End If
            If .Cells(1, i) = "Audit/Support SW" Then
                supSW = i
            End If
 
            'Check for the ol' ifs
            If edited(1, i) = "" And .Cells(1, i) = "CMMI Last Audit-Like Activity Date" Then
                If edited(1, i - 1) <> "N/A" Then
                    'This interior conditional statement simply prevents leading commas
                    If list.Range("D" & targetrow) <> "" Then
                        list.Range("D" & targetrow) = list.Range("D" & targetrow) & ", " & .Cells(1, i)
                    Else
                        list.Range("D" & targetrow) = .Cells(1, i)
                    End If
                End If
            ElseIf edited(1, i) = "" And .Cells(1, i) = "PM/Peer review/waiver Date" Then
                If edited(1, i - 1) <> "No" Then
                    If list.Range("D" & targetrow) <> "" Then
                        list.Range("D" & targetrow) = list.Range("D" & targetrow) & ", " & .Cells(1, i)
                    Else
                        list.Range("D" & targetrow) = .Cells(1, i)
                    End If
                End If
            ElseIf edited(1, i) = "" And .Cells(1, i) <> "" And .Cells(1, i) <> "Comments" And _
                    .Cells(1, i) <> "APT BASELINE NEEDED?" Then
                If list.Range("D" & targetrow) <> "" Then
                    list.Range("D" & targetrow) = list.Range("D" & targetrow) & ", " & .Cells(1, i)
                Else
                    list.Range("D" & targetrow) = .Cells(1, i)
                End If
            End If
 
            'Check for N/As
            If edited(1, i) = "N/A" Or edited(1, i) = "n/a" Then
                Select Case .Cells(1, i)
                    'First group of exceptions checks Audit CMMI (the index was stored earlier)
                    Case "CMMI Last Audit-Like Activity Date", "Task CMMI", "CMMI Audit Start Month", _
                            "CMMI Audit Stop Month", "Avg CMMI Effort per MONTH!!"
                        If edited(1, audCM) = "No" Then
                            list.Range("E" & targetrow) = list.Range("E" & targetrow)
                        End If
                    'Second checks Audit/Support SW (index stored earlier)
                    Case "Task SW"
                        If edited(1, supSW) = "No" Then
                            list.Range("E" & targetrow) = list.Range("E" & targetrow)
                        End If
                    'These fields simply require no reporting
                    Case "Comments", "CMMI Audit Status", "PE/LSYE", "LEE", "LSE"
                        list.Range("E" & targetrow) = list.Range("E" & targetrow)
                    'Report everything else
                    Case Else
                        If list.Range("E" & targetrow) <> "" Then
                            list.Range("E" & targetrow) = list.Range("E" & targetrow) & ", " & .Cells(1, i)
                        Else
                            list.Range("E" & targetrow) = .Cells(1, i)
                        End If
                End Select
            End If
        Next i
    End With
End Sub

This is the other one called in the change event

Sub summarize()
    Application.ScreenUpdating = False
 
    'Sheets("Summary").Select
    pdfinal = Sheets("ProjectData").Range("A" & Rows.Count).End(xlUp).Row
    sumfinal = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row
    pdfinalcol = Sheets("ProjectData").Cells(1, 1).End(xlToRight).Column
    sumfinalcol = Sheets("Summary").Cells(2, 3).End(xlToRight).Column
 
    'Find the date and effort columns
    With Sheets("ProjectData")
        For x = 1 To pdfinalcol
            Select Case .Cells(1, x)
            Case "CMMI Audit Stop Month"
                stopmonth = x
            Case "CMMI Audit Start Month"
                startmonth = x
            Case "Avg CMMI Effort per MONTH!!"
                effort = x
            Case "QE"
                qe = x
            End Select
        Next x
    End With
 
    With Sheets("Summary")
        For i = 1 To sumfinal
            If .Range("B" & i) = "CMMI" Then
                For ii = 3 To sumfinalcol
                    .Cells(i, ii).ClearContents
                    For x = 1 To pdfinal
                        If InStr(1, Sheets("ProjectData").Cells(x, qe), .Cells(i - 2, 2), vbTextCompare) <> 0 And _
                                Sheets("ProjectData").Cells(x, startmonth).Value < .Cells(2, ii).Value And _
                                Sheets("ProjectData").Cells(x, stopmonth).Value > .Cells(2, ii).Value Then
                            .Cells(i, ii).Value = .Cells(i, ii).Value + Sheets("ProjectData").Cells(x, effort).Value
                        End If
                    Next x
                    If .Cells(i, ii) = "" Then
                        .Cells(i, ii) = 0
                    End If
                Next ii
            End If
        Next i
    End With
End Sub

 

[Jonmo1]

Just a comment, sorry I have no idea what the problem is.

That is very nicely organized code, and you used code tags correctly.
No one will EVER complain about seeing a long code if it's presented like that.
Especially with the way the new forum software displays code with a scroll bar...
If anyone does, they should be flogged(and ignored)...

So generally speaking it is better to post code that is not needed to be seen, then to Not post it when it is needed...

One of my favorite sayings
"Better to have and not need, then to need and not have"

 

[Legacy 96851]

Just a comment, sorry I have no idea what the problem is.

That is very nicely organized code, and you used code tags correctly.
No one will EVER complain about seeing a long code if it's presented like that.
Especially with the way the new forum software displays code with a scroll bar...
If anyone does, they should be flogged(and ignored)...

So generally speaking it is better to post code that is not needed to be seen, then to Not post it when it is needed...

One of my favorite sayings
"Better to have and not need, then to need and not have"

Well thanks for the compliment
Sorry to anyone trying to help that I didn't explain it, it may be a little long to just read from beginning to end without any guidance. If I have time later, I'll bump this thread with an explanation.