vba code to insert new row based on two columns condition-CodePudding

I would like to insert a row when there is a value change in two-column cells in an excel For example, columns F and G where F has a string value of "Buy" and "Sell" and column G has string "D" and "S".

My code creates a blank row only on one column condition, which is when the value changes a blank row is added.

Sub InsertRowsAtValueChange()

Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

But the code should add a row when the column is sorted and when the last value is "Buy" - "D" "Buy" - "S" "Sell" - "D" "Sell" - "S"

The image shows what I am trying to achieve.Code should work to create the bottom requirement

CodePudding user response：

Seems pretty straight forward, you just need to add an OR clause to your if condition:

Sub InsertRowsAtValueChange()

Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value _
    Or WorkRng.Cells(i, 2).Value <> WorkRng.Cells(i - 1, 2).Value Then
       
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

(I added the "_" just to wrap it onto the next line for readability)