TransWikia.com

This VBA script to remove duplicates across worksheets isn't quite working correctly for me

Super User Asked by Dyllan Williams on January 26, 2021

This is a code I found online that almost works.

The problem is it only checks one specified column for duplicates, and I need it to check several columns. Where it is stating Range for column A or B, I need to state column A, B, C, D, E, F, I, and J. It then needs to delete entire row from certain sheet if found in any other.

I’ve tried a couple ways but cant seem to get it to reference multiple columns without errors. Also, Is there a simple way to reference all sheets (I have 5) to each other instead of running this code multiple times referencing one sheet to another individually?

Sub Remove_Duplicate TEST1()
Application.ScreenUpdating = False

Dim startRow As Integer
startRow = 2

Dim row As Integer
row = startRow

Dim bRow As Integer

Do While (Worksheets("Sheet1").Range("A" & row).Value <> "")

    Dim aVal As String
    Dim bVal As String

    aVal = Worksheets("Sheet1").Range("A" & row).Value
    bVal = Worksheets("Sheet1").Range("B" & row).Value


    bRow = startRow

    Do While (Worksheets("Sheet2").Range("A" & bRow).Value <> "")

    Dim aVal2 As String
    Dim bVal2 As String

    aVal2 = Worksheets("Sheet2").Range("A" & bRow).Value
    bVal2 = Worksheets("Sheet2").Range("B" & bRow).Value

    If (aVal = aVal2 And bVal = bVal2) Then

        Worksheets("Sheet1").Rows(row).Delete 
        row = row - row
        Exit Do

    End If

    bRow = bRow + 1

    Loop

row = row + 1
Loop

End Sub

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP