Worksheet Change Event Intersect Error 91

Re: Run Time Error 91

You have not defined Target. i.e you will need to set target to a Range object

Re: Run Time Error 91

If the result of the intersect is nothing then the second part of the test will fail.

Also the second part of the test will not do what you want. It will not check all cells in the rather being empty.

[vba] If Intersect(Target, Range("D6:D6000")) Is Nothing Or _
Application.WorksheetFunction.CountA(Range("D6:D6000")) = 0 Then
If Intersect(Target, Range("F6:F6000")) Is Nothing Or _
Application.WorksheetFunction.CountA(Range("F6:F6000")) = 0 Then
If Intersect(Target, Range("I6:I6000")) Is Nothing Or _
Application.WorksheetFunction.CountA(Range("I6:I6000")) = 0 Then
Exit Sub
Else

MsgBox "Do Something......"
End If
End If
End If
[/vba]

Re: Worksheet Change Event Intersect Error 91

Helmekki,

Please take care with your thread titles.

Can your code be simplified to this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D6:D6000,F6:F6000,I6:I6000")) Is Nothing Or _
       Application.WorksheetFunction.CountA(Range("D6:D6000,F6:F6000,I6:I6000")) = 0 Then
        Exit Sub
    Else
        MsgBox "Do Something......"
    End If
End Sub

Re: Worksheet Change Event Intersect Error 91

I am hoping someone will have a better idea but if not take a look at this

http://www.dailydoseofexcel.co…/21/capture-deleted-rows/

Re: Worksheet Change Event Intersect Error 91

I havnt tested this (But its an idea):

Public glOldRows As Long
Private Sub Worksheet_Activate()
    glOldRows = Me.UsedRange.Rows.Count
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Me.UsedRange.Rows.Count <> glOldRows Then
        If Intersect(Target, Range("D6:D6000,F6:F6000,I6:I6000")) Is Nothing Or _
           Application.WorksheetFunction.CountA(Range("D6:D6000,F6:F6000,I6:I6000")) = 0 Then
            Exit Sub
        Else
            MsgBox "Do Something......"
        End If
    End If


    glOldRows = Me.UsedRange.Rows.Count


End Sub

Re: Worksheet Change Event Intersect Error 91

Quote from helmekki

Hi There
This is my code:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
If Intersect(Target, Range("F6:F6000")) Is Nothing Or Intersect(Target, Range("F6:F3000")) = "" Then
If Intersect(Target, Range("I6:I6000")) Is Nothing Or Intersect(Target, Range("I6:I6000")) = "" Then
Exit Sub
Else


Do Something......
End If
End If
End If
End Sub

Display More

when excution comes to this line

Code

If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then

a run time error (91) appears, it tells object variable or with block variable not set.

any idea why ? Thank you

Display More

Your original code was not a Worksheet change event, hence my response that Target
was NOT defined ...

You had

Sub T()
etc .............

Re: Worksheet Change Event Intersect Error 91

helmekki, if you must move the goal posts at least post to say as much.