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

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History