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!

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 headersA B 1 A A 2 B C 3 D D 4 E F 5 G GThe 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 Ginserting 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 BCell.Offset(1).Rows.EntireRow.Insert Range("B" & BCell.Row).Copy Range("B" & BCell.Row + 1) BCell.Clear End If End If Next BCell End Sub

message edited by AlwaysWillingToLearn

This is great, thank you!

No worries glad it worked

Ask Your Question

Weekly Poll