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."
The issue I run into is how my work is formatted. Every entry takes a total of 3 lines to complete. Example Below:
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
When this happens it will throw it all off and I will sometimes end up with something that looks like this:
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
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