Comparing two columns with VBA, inserting blank rows moving associated data

Saria Ahmad

New Member
Joined
Feb 23, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Hi folks, I am quite new to VBA. I need help in comparing the data in two columns. I want to bring similar data in same row and if nothing common is found I want to insert the blank row. I tried many ways but all in vain.
sample data file is attached below
Below is my code.
Looking forward for your valuable suggestions.


Sub compare()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A11")
Set rng2 = Range("B1:B8")

If rng1.Value1 = rng2.Value2 Then
'both cells dont match.'
Else
Worksheets(1).Rows(1).Insert
End If
End Sub
 

Attachments

  • Annotation 2021-02-23 122257.png
    Annotation 2021-02-23 122257.png
    27.4 KB · Views: 8

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Hi, could you help me in writing vba code for this sort of data. Idea behind dealing with this kind of data is to make one reference column, bring data on the same line(row). insert a blank line instead. For instance. I have 5 columns to compare with each other and with among 5 column I want to assign one reference column and compare the rest 4 columns with the reference column 1 by 1. If duplicates occur in the same column just appear as it is
That sounds like an entirely different question than your original one.
As such, you should post it to its own new thread.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,127,313
Messages
5,623,935
Members
416,001
Latest member
teabag

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top