Hello to the community,
I have some data in two excelexcel sheet. in this two excel sheet there is some data in Sheet2, and that Sheet2 data is not matching with Sheet1, by using below code i find the unwanted entry matching with Sheet1
Sheet 1 Sheet2
| Column A | Column B | Column C | Column A | Column B | Column C | |
|---|---|---|---|---|---|---|
| 1 | ABC | A | 1 | YZAA | I | |
| 2 | DEF | B | 2 | ABC | A | |
| 3 | JHI | C | 3 | DEF | B | |
| 4 | JKL | D | 4 | JHI | C | |
| 5 | MNO | E | 5 | ABAC | J | |
| 6 | PQR | F | 6 | JKL | D | |
| 7 | STU | G | 7 | MNO | E | |
| 8 | VWX | H | 8 | PQR | F | |
| 9 | STU | G | ||||
| 10 | VWX | H |
Now by running the below code i insert the row above the unmatched row.
Sub M1()
'=====================================================
'Below code will insert unmatched record from _
Sheet2 sheet.
'=====================================================
Const sh1Col As String = "C" ' << sheet1 data in col A, change
Const sh2Col As String = "C" ' << sheet2 data in col A, change
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Long, r2 As Long
Application.WindowState = xlNormal
Windows("Sample2.xlsm").Activate
Sheets("Sheet2").Select
Set ws1 = Sheets("Sheet2")
Application.WindowState = xlNormal
Windows("Sample1.xlsm").Activate
Sheets("Sheet1").Select
Set ws2 = Sheets("Sheet1")
r1 = ws1.Cells(Rows.Count, sh1Col).End(xlUp).Row
r2 = ws2.Cells(Rows.Count, sh2Col).End(xlUp).Row
On Error Resume Next
For i = r1 To 2 Step -1
For Each r In ws2.Range(sh2Col & "3:" & sh2Col & r2)
If ws1.Cells(i, sh1Col).Value = r.Value Then GoTo myNext
Next r
ws1.Cells(i, sh1Col).EntireRow.Insert
myNext:
Next i
End Sub
Output is below.
Sheet 1 Sheet2
| Column A | Column B | Column C | Column A | Column B | Column C | |
|---|---|---|---|---|---|---|
| 1 | ABC | A | ||||
| 2 | DEF | B | 1 | YZAA | I | |
| 3 | JHI | C | 2 | ABC | A | |
| 4 | JKL | D | 3 | DEF | B | |
| 5 | MNO | E | 4 | JHI | C | |
| 6 | PQR | F | ||||
| 7 | STU | G | 5 | ABAC | J | |
| 8 | VWX | H | 6 | JKL | D | |
| 7 | MNO | E | ||||
| 8 | PQR | F | ||||
| 9 | STU | G | ||||
| 10 | VWX | H |
Now by running below code i manage to highlight the unwanted row only once
Application.WindowState = xlNormal
Windows("Sample2.xlsm").Activate
Sheets("Sheet2").Select
ActiveSheet.Range("A9:A5000").Find("").Select
ActiveCell.Offset(1).Select
ActiveCell.Offset(0, 86).Select
Range(Selection, Cells(ActiveCell.Row, 1)).Select
'Range(Selection, Selection.End(xlUp).Offset(1)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
End With
Output is below.
Sheet 1 Sheet2
| Column A | Column B | Column C | Column A | Column B | Column C | |
|---|---|---|---|---|---|---|
| 1 | ABC | A | ||||
| 2 | DEF | B | 1 | YZAA | I | |
| 3 | JHI | C | 2 | ABC | A | |
| 4 | JKL | D | 3 | DEF | B | |
| 5 | MNO | E | 4 | JHI | C | |
| 6 | PQR | F | ||||
| 7 | STU | G | 5 | ABAC | J | |
| 8 | VWX | H | 6 | JKL | D | |
| 7 | MNO | E | ||||
| 8 | PQR | F | ||||
| 9 | STU | G | ||||
| 10 | VWX | H |
Now my question is as below
i wanted to create a code in vbaVBA that run same task multiple times without having to write code for each of the tasks.
means the code should highlighted all unmatched row.
i have a code for delete empty rows and then i have a code to sort the data as per the color, and after the conformation of highlighted rows i have a code to delete it.
So, requesting to entire community to help me for my issue.
Thank you
Hemal
4