Event Enabled Macro - Change after inserting row

[zack_]

Good Morning Everyone!

This is the first time I have ever attempted this sort of macro. I am more or less testing the logic to see if it works. The macro appears to be working, but I was wondering if anyone saw potential pitfalls in the code.

Eventually, I plan to add it to a worksheet so that when rows are inserted within a specified range it will be take the formulas from the above row and copy them down to the newly inserted row.

Here is the code, thanks in advance for taking a look!

Private Sub Worksheet_Change(ByVal Target As Range)


Dim iFinalRow As Integer
iFinalRow = Cells(Rows.Count, 1).End(xlUp).Row


' Is target in desired range AND has the range expanded due to rows being inserted


If Not Application.Intersect(Target, Me.Range(Cells(5, 1), Cells(iFinalRow, 1))) Is Nothing And _
   Target.CurrentRegion.End(xlDown).Row <> iFinalRow Then
     
  ' At least one cell of Target is within the range. Change above row color
     
     Range("a1").Value = "Blue"
           
Else
        ' No cell of Target is in the range A5: Last row with data. Get Out.
    
     Exit Sub
End If


End Sub

 

[BarryL]

I would change Integer to Long in case your data increases beyond the scope of an Integer variable.

 

[zack_]

I would change Integer to Long in case your data increases beyond the scope of an Integer variable.

Thanks Barry! Aside from that does the logic seem sound?

 

[BarryL]

the below looks incorrect as you will already have inserted the row meaning the iFinalRow will equal your target region

 

[BarryL]

the below looks incorrect as you will already have inserted the row meaning the iFinalRow will equal your target region

Target.CurrentRegion.End(xlDown).Row <> iFinalRow Then

 

[zack_]

the below looks incorrect as you will already have inserted the row meaning the iFinalRow will equal your target region

hey barry,

could you explain a little more? I think I may be confused on the order of how the program is read. in my head, the variable is created and then as the row is inserted the current region allows the entire range to be selected and the last row found, which is one greater than ifinalrow....

but I guess what you are saying, is the variable is not created until after the event, inserting the row, so the two are the same?

 

[BarryL]

but I guess what you are saying, is the variable is not created until after the event, inserting the row, so the two are the same?

Exactly, so you'll insert the row then the worksheet event will run assigning the variable value.

you can assign the last row value upon activation of the sheet like below

Option Explicit
Public lr As Long
Private Sub Worksheet_Activate()
Let lr = Range("A" & Rows.Count).End(xlUp).Row
End Sub
Sub test()
MsgBox lr
End Sub

 

[BarryL]

then you can use lr in your other change worksheet event without having to assign a value to it.

 

[BarryL]

Option Explicit
Public lr As Long
Private Sub Worksheet_Activate()
Let lr = Range("A" & Rows.Count).End(xlUp).Row
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

' Is target in desired range AND has the range expanded due to rows being inserted


If Not Application.Intersect(Target, Me.Range(Cells(5, 1), Cells(iFinalRow, 1))) Is Nothing And _
   Target.CurrentRegion.End(xlDown).Row <> lr Then
     
  ' At least one cell of Target is within the range. Change above row color
     
     Range("a1").Value = "Blue"
           
Else
        ' No cell of Target is in the range A5: Last row with data. Get Out.
    
     Exit Sub
End If


End Sub

 

[zack_]

Exactly, so you'll insert the row then the worksheet event will run assigning the variable value.

you can assign the last row value upon activation of the sheet like below

Option Explicit
Public lr As Long
Private Sub Worksheet_Activate()
Let lr = Range("A" & Rows.Count).End(xlUp).Row
End Sub
Sub test()
MsgBox lr
End Sub

Awesome. So from there, I would just call that variable from the worksheet change event macro, and compare it to the target range to see if it was expanded? ... thanks a lot! i think this makes sense. i'm going to tinker around and test. i appreciate all the quick feedback Barry