Insert Lines Every nth Row

traelor

New Member
Joined
Aug 12, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

I year ago I asked this question and I got a great answer but now I need some help creating a conditional statement within the code:
The code below will in its current state insert "x" amount of rows dependent on the specified "row interval."

VBA Code:
Sub InsertRowsAtIntervals()

'Updateby Extendoffice
    Dim Rng As Range
    Dim xInterval As Integer
    Dim xRows As Integer
    Dim xRowsCount As Integer
    Dim xNum1 As Integer
    Dim xNum2 As Integer
    Dim WorkRng As Range
    Dim xWs As Worksheet
   
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    xRowsCount = WorkRng.Rows.Count
EX: xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
    xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
    If xInterval <= xRows Then
        MsgBox "Interval needs to be greater then " _
             & "number of the inserted rows.": GoTo EX
    End If
    For i = 1 To Int(xRowsCount / xInterval)
        Rows("" & WorkRng.Row + i * xInterval & ":" & _
                  WorkRng.Row + i * xInterval + xRows - 1 & ""). _
                  Insert Shift:=xlDown
    Next

End Sub

The issue I run into is how my work is formatted. Every entry takes a total of 3 lines to complete. Example Below:

DEBIT
JOES AUTO PARTS
CREDIT
MILLYS BOOTS


Some times I will run into scenarios where this code will split my work, due to descriptions running on into 3 cells--not just 2. Example Below

DEBIT
UNITEDHEALTH CARE OF AMERICA
HEALTH INSURANCE PREMIUMS

When this happens it will throw it all off and I will sometimes end up with something that looks like this:

DEBIT
UNITEDHEALTH CARE OF AMERICA
[Row inserted from Code]
[Row inserted from Code]
[Row inserted from Code]
HEALTH INSURANCE PREMIUMS



What I am looking for:

What I am looking for is some way include a condition that it will insert rows only if one or both of the adjacent cells, at the point of insertion, are blank.
And if they are not then shift the upper rows down until the 1st condition is met.


I am admittedly not a VBA Expert as I am sure this is very easy to do but I am trying to understand and learn
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top