Solved Macro to insert row based on two matching rows

June 6, 2016 at 06:24:00
Specs: Windows 10
Hello, I have a spreadsheet with two columns. Column A has a list of companies we work with and Column B has a list of companies we have contracts with. I am trying to see if I can run a macro that will see if the two rows match, and if it doesn't, it inserts a row so that the matching company name could be added. Is there any way to do this?
Essentially I need something that says, If A1<>B1, then insert a new B1 and shift B1 down to B2. Thanks in advance!

See More: Macro to insert row based on two matching rows

Reply ↓  Report •

June 6, 2016 at 07:17:42
✔ Best Answer
Ok I have had a go, it works but the code isn't very pretty....

So I have the following data set

Please note the top A and B represent Excel column headers

         A     B
1        A     A
2        B     C
3        D     D
4        E     F
5        G     G

The code the turns the above into the following

          A    B
1         A    A
2         B
3              C         
4         D    D
5         E
6              F
7         G    G

inserting a new row for non matching rows, then moving the row value down in column B to allow you to add the matching name in column A

Sub ShiftRows()
    Dim BCell As Range
    For Each BCell In Range("B1", Range("B" & Rows.Count).End(xlUp))
        If BCell.Value <> Empty And BCell.Offset(0, -1) <> Empty Then
            If BCell.Value <> BCell.Offset(0, -1).Value Then
                Range("B" & BCell.Row).Copy Range("B" & BCell.Row + 1)
            End If
        End If
    Next BCell
End Sub

message edited by AlwaysWillingToLearn

Reply ↓  Report •

June 6, 2016 at 07:59:09
This is great, thank you!

Reply ↓  Report •

June 8, 2016 at 00:15:31
No worries glad it worked

Reply ↓  Report •

Related Solutions

Ask Question