Solved: Change range in code

[Shazam]

This is probably a easy question. But how to have this worksheet event code to start at B2 and down. Right now its working at the whole column B I just would like to start at B2:B40000

 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.DisplayAlerts = False
Dim rng As Range
If Target.Count > 2 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Target.Offset(0, -1).FillDown 'A
Target.Offset(0, 2).FillDown 'D
Target.Offset(0, 3).FillDown 'E
Target.Offset(0, 4).FillDown 'F
Target.Offset(0, 11).FillDown 'M
Target.Offset(0, 12).FillDown 'N
Target.Offset(0, 13).FillDown 'O
Target.Offset(0, 14).FillDown 'P

Application.DisplayAlerts = True
GetOut:

End Sub

[Zack Barresse]

[vba]If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then exit sub[/vba]

[mdmackillop]

Hi Shazam
You need to disable Events or your code will restat when the first copydown occurs.
It's neater to use an array and loop through for code like this

[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.EnableEvents = False
Dim Cols, c
Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14)
Dim rng As Range
If Target.Count > 2 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
For Each c In Cols
Target.Offset(0, c).FillDown
Next
GetOut:
Application.EnableEvents = True
End Sub

[/vba]

[Shazam]

Thanks to you both.

mdmackillop,

I have seen the Application.EnableEvents = False Before but I just dont really understand what does it do. Can you give me some secnarios so I could have a better understanding of it?

Thanks!

Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range) 
    On Error Goto GetOut 
    Application.EnableEvents = False 
    Dim Cols, c 
    Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14) 

   If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then exit Sub 
    For Each c In Cols 
        Target.Offset(0, c).FillDown 
    Next 
GetOut: 
    Application.EnableEvents = True 
End Sub 

[mdmackillop]

An Event macro will run whenever the apprpriate Event occurs. In your case, whenever the worksheet changes. As Target.Offset(0, c).FillDown changes the worksheet, your code will stop and the Event code starts again, leading to errors/freezing. Try stepping through the code to see what happens.
As soon as you have checked that your macro should run, you should then disable events. You must re-enable them, as this will not happen automatically, and your error handling should also re-enable them before exiting the routine.

[Shazam]

Thanks mdmackillop I will follow your instructions.

[mdmackillop]

Hi Shazam
You should check validity and exit before your main code is run. You also have to avoid Exit Sub after you have disabled events. To terminate the code you must use a GoTo GetOut method in order to re-enable events.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
'Check validity
If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then Exit Sub

On Error GoTo GetOut
Application.EnableEvents = False
Dim Cols, c
Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14)
For Each c In Cols
Target.Offset(0, c).FillDown
Next
GetOut:
Application.EnableEvents = True
End Sub[/vba]