1

Here is what I tried. It works but doesn't work automatically. You have to run the macro to get it to work. I need it to work as data is entered.

Sub BlankLine()

    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long

    Col = "K"
    StartRow = 1
    BlankRows = 1

        LastRow = Cells(Rows.Count, Col).End(xlUp).Row

        Application.ScreenUpdating = False

        With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) <> "" Then
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True

End Sub
MSNewUser1
  • 11
  • 2
  • 1
    http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change – Tim Wilkinson Mar 14 '17 at 14:34
  • Possible duplicate of [automatically execute an Excel macro on a cell change](http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) – Jordan Mar 14 '17 at 14:35
  • jordan - thanks, i looked at that but it doesn't work. The code works but the user has to know to run the macro. How do i get the worksheet to auto-run the code for the entire sheet based upon the – MSNewUser1 Mar 14 '17 at 15:24

0 Answers0