Run Macro Upon New Row Insert Using VBA

[Stryker152]

Hello,

I am trying to have a macro run in another sheet in my workbook when I insert a new row into my main sheet in the same workbook. Here is the VBA code that I have been using but I cannot figure out how to say that when a new row is inserted run this macro. Here is the code:

Private Sub worksheet_change(ByVal target As Range)
If 'Row insert = True'
Then Application.Run "Sheet#.MacroName"
End Sub

Any help with this would be greatly appreciated.

 

[dmt32]

Hi,
As far as I am aware, there is no built-in function that detects when a row is added / deleted.

You can make a workaround by using the sheets calculate event to do what you want but this will require a couple of helper cells in the worksheet that will remain static.

In a standard module add this code:

 Public Function LastRow() As Long    
    Application.Volatile
    ActiveSheet.AutoFilterMode = False
    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function

In your worksheets code page add this code:

 Private Sub Worksheet_Calculate()    
     On Error GoTo myerror
    Application.EnableEvents = False
    If Me.Range("H1").Value <> Me.Range("I1").Value Then
        Me.Range("I1").Value = Me.Range("H1").Value
        
        'Call your macro here
    End If


myerror:
    Application.EnableEvents = True
End Sub

In this example I use cells H1 & I1 as the helper cells but you can amend code as required. You should also add the call to your macro where shown in RED.

In Cell H1 of your worksheet enter formula =LastRow() & press enter.
The last row number with data should be displayed in both H1 & I1

When you Add or Delete a row in your worksheet, the value in H1 should change & is it no longer matches the value in I1, I1 is updated & your macro is called.

Hope Helpful

Dave

 

[Stryker152]

Hello Dave,

Thanks for the response. I pasted the code for the standard module macro into the "This Workbook" code page, and I pasted the second macro into the specific sheet where I would have my macro run upon a new row inserted. When I make cell H1 =LastRow() I receive the #NAME? error. I am not exactly sure what I might be doing wrong. If you could help me with this, that would be great.

 

[dmt32]

The function needs to go into a STANDARD module.

From VB editor menu Insert > Module.

Dave

 

[Stryker152]

Hi Dave,

Thanks for the reply, and I greatly appreciate the help. I have the macro working now. I have one last question. When I double click a cell in any sheet in the same workbook and then select another cell in the same sheet the macro will run. Is there a way I can have it only run when I insert or delete a row in a specific table in one sheet. Thanks.

 

[dmt32]

As said, there is no built in function to detect row insert / delete activity - Idea is just a limited trick - code will run when you add data that increases the range which changes value of Lastrow.

Dave

 

[Stryker152]

Thanks Dave,

I was wondering if there is a way that I could change the code slightly to only detect a change in the range for cell H1 and I1 in only one sheet. Other than that, everything is working great! Thanks.

 

[dmt32]

Hi,
try something like this:

Private Sub Worksheet_Calculate()
    On Error GoTo myerror
    
    If ActiveSheet.Name <> "Sheet1" Then Exit Sub
    
    Application.EnableEvents = False
    If Me.Range("H1").Value <> Me.Range("I1").Value Then
        Me.Range("I1").Value = Me.Range("H1").Value
        'MsgBox ""
        'Call your macro here
    End If


myerror:
    Application.EnableEvents = True
End Sub

change sheet name shown in RED as required.

Dave

 

[Stryker152]

Dave,

Thanks for the help. The macro runs perfectly. The only change that I made was in the IF statement. Instead of (If ActiveSheet.Name <> "Sheet1" Then Exit Sub), I used (If ActiveSheet.CodeName <> "Sheet1" Then Exit Sub). The codename allows the macro to run even if the sheet name is changed. Thanks again.