CODEWITHSUNDEEP
vbaexcel
Robot_Zulu
Robot_Zulu

Reputation: 1

VBA Excel - Auto run macro to insert new blank row after cell above has value entered

I am trying to get a macro to auto run to:

  1. insert a blank row in each section e.g. Architectural when the data validation row above (in column A) has a value entered into it. I entered the code as a sub in the worksheet, when I click run in the developer tab in excel, it inserts a line once, but I would like it to run automatically (after the workbook is opened) every time something is entered into column A.

Print Screen of Excel 

Sub BlankLine()
    'Updateby20150203
    Dim Rng As Range
    Dim WorkRng As Range

    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Set WorkRng = WorkRng.Columns(1)

    xLastRow = WorkRng.Rows.count
    Application.ScreenUpdating = False

    For xRowIndex = xLastRow To 1 Step -1
        Set Rng = Range("B" & xRowIndex)
        If Rng.Value = "" = False Then
            Rng.Offset(1, 0).EntireRow.Insert Shift:=xlDown
        End If
    Next

    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 2256

Answers (1)

Axel
Axel

Reputation: 2725

I think I can help you with your first question.

You can automatically start a macro when a cell changes with a Sub Worksheet_Change(ByVal Target As Range) Sub inside the worksheet.

Here is description: https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel

You can insert a new row with the following code:

Application.Selection.EntireRow.Insert shift:=xlDown

When you do just that, you will encounter that the new line will again trigger the event to start the macro, hence again inserting a new line. This leads to an infinity loop. To stop this from happening, we need to disable events for the time of the change.

Application.EnableEvents = False
Call new_line_below_selection_macro
Application.EnableEvents = True

Here is a question with a similar problem: How to end infinite "change" loop in VBA

I hope this helps.

Here is the code which should go into the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("A1:C10") 'Area this should apply

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        Application.EnableEvents = False
        'either you put your code here
        'Application.Selection.EntireRow.Insert shift:=xlDown
        'or you call it from a module
        Call Module1.BlankLine
        Application.EnableEvents = True

    End If
End Sub

Upvotes: 1

Related Questions