Excel vba to function as "hit an Enter" key

Question

Hello,

I would like to add a vba code in Excel to go through the data from start of the row to end of the rows with data with the following search and perform.

If any row that do not have a blank row before PROGRAM STARTED  row, add a blank row (Function like put a cursor before PROGRAM and hit enter) before PROGRAM STARTED. Sample is something like below.

Before:

PROGRAM STARTED

PROGRAM NAME: 132Z

START TIME: 2017/02/08 06:43:01

END TIME: 2017/02/08 07:28:53

PROGRAM ENDED

END TIME: 2017/02/08 10:11:31

PROGRAM ENDED

END TIME: 2017/02/08 11:08:58

PROGRAM ENDED

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 12:59:31

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 13:01:11

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 13:04:42

END TIME: 2017/02/08 13:20:48

PROGRAM ENDED

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 13:24:15

END TIME: 2017/02/08 13:37:45

PROGRAM ENDED

END TIME: 2017/02/28 16:36:22

PROGRAM ENDED

END TIME: 2017/02/28 17:27:11

PROGRAM ENDED

After:

PROGRAM STARTED

PROGRAM NAME: 132Z

START TIME: 2017/02/08 06:43:01

END TIME: 2017/02/08 07:28:53

PROGRAM ENDED

END TIME: 2017/02/08 10:11:31

PROGRAM ENDED

END TIME: 2017/02/08 11:08:58

PROGRAM ENDED

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 12:59:31

---

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 13:01:11

---

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 13:04:42

END TIME: 2017/02/08 13:20:48

PROGRAM ENDED

PROGRAM STARTED

PROGRAM NAME: 113W

START TIME: 2017/02/08 13:24:15

END TIME: 2017/02/08 13:37:45

PROGRAM ENDED

END TIME: 2017/02/28 16:36:22

PROGRAM ENDED

END TIME: 2017/02/28 17:27:11

PROGRAM ENDED

Answer 1

Hi Cancafe

Try this macro

****************************************************************************

Sub InsertBlankRow()

Dim Stp As Range

Dim MyRng As Range

Dim Criteria As String

On Error GoTo Stp1  '''Error handling

''' Choose your Starting Point Cell

Set Stp = Application.InputBox(Prompt:="Please Select a Cell Starting Point and Criteria", Title:="Starting Point-Criteria", Type:=8)

Criteria = Stp.Value

Set MyRng = Range(Cells(Stp.Row, Stp.Column), Cells(Rows.Count, Stp.Column).End(xlUp))

Application.ScreenUpdating = False

'' This part will evaluate each cell in the range and look for the Criteria

For Each x In MyRng

        If x.Value = Criteria And x.Offset(-1, 0).Value <> "" Then

                x.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

        End If

Next x

Application.ScreenUpdating = True

MsgBox "Done"

Stp1:

End Sub

******************************************************************

Notes : With this macro you can choose the criteria above which you want to insert a blank row

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, Please, share your appreciation by marking it as answered. I would be grateful to you as well.

Thanks

Regards

Jeovany CV

Answer 2

Hi camcafe

Hope you are ok

You reached out the community looking for help

And I just wonder if you have checked or tried the solution above?

Did you find solution to your question elsewhere?

Do you require any further help on this?

Let us know

Answer 3

I will be trying the code and update the response once I know the results

Answer 4

Hi Jeovany,

I tried the code and it is doing anything. In the code I don't see where it has the criteria of "PROGRAM STARTED" without the blank row before it.

Also, the starting point always start is cell A1.

Answer 5

The previous macro give you the possibility

1-Have your data in any other column

2- To run it anywhere in the workbook without the need of changing the code

3- Insert a blank row above any other criteria. The one you choose with the Input box.

But if you always have the same situation then

This is the macro

*******************************************************************

Sub InsertBlankRow()

Dim MyRng As Range

Dim Criteria As String

Application.ScreenUpdating = False

Criteria = "PROGRAM STARTED"

Set MyRng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))

For Each x In MyRng

        If x.Value = Criteria And x.Row = 1 Then

              x.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

        End If

        If x.Value = Criteria And x.Offset(-1, 0).Value <> "" Then

                x.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

        End If

Next x

Application.ScreenUpdating = True

MsgBox "Done"

End Sub

*******************************************************************************

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, Please, share your appreciation by marking it as answered. I would be grateful to you as well.

Thanks

Regards

Jeovany CV