Loop through all worksheets in chosen folder to remove duplicate rows

[1819]

I would be very grateful if this code could be amended, so that it will loop through all worksheets in all workbooks in a chosen folder.

Right now, it is only comparing two hardcoded worksheets.

Many thanks.



Option Explicit

Sub WalkThePlank()
Application.ScreenUpdating = False

Dim startRow As Integer
startRow = 1

Dim row As Integer
row = startRow

Dim bRow As Integer

'sharks below cap'ain
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


'I see thy booty
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 ' we found a traitor, feed em to the sharks
row = row - row
Exit Do

End If

bRow = bRow + 1

Loop

row = row + 1
Loop

End Sub

[Leith Ross]

Hello 1819,

Are you only comparing columns "A" and "B" on each sheet?

Is there a master sheet to which all other sheets will compared?

Are the sheets all named the same in all the workbooks?

[1819]

Thank you for your response, Leith.

It's actually only necessary to compare column A.

Yes, there is a master sheet to which all other sheets will be compared.

No, the sheet names will change in each workbook. All will be different.

Hope that helps and thank you.

[Leith Ross]

Hello 1819,

Help me out here. Is the column "A" data compared to every other column with data on the sheet or is column "A" of the master sheet compared to column "A" of the other sheets?

If the sheet names are all different how do I know which sheet will be the master?

[SamT]

Dim Wb As Workbook 'To be opened in Dir Loop
Dim MSht As Worksheet
Dim MShtName As String
Dim ws As Worksheet

Dim MList as Variant
Dim SList As Variant

Dim i as long
Dim j As Long
Dim r as Long


MShtName = "Master Worksheet Name Here"

'Start Dir loop to open workkbooks heere

With Wb
Set Msht = .Sheets(MshtName)

With MSht
Mlist = .Range(Range("A1"), .Cells(Rows.Count,"B").End(xlUp)).Value
End With

For Each ws in .WorkSheets
If not ws Is Msht then

With ws
SList = .Range(Range("A1"), .Cells(Rows.Count,"B").End(xlUp)).Value

For i = Lbound(Mlist(1)) to Ubound(MList(1))
For j = Lbound(Slist(1)) to Ubound(SList(1))
If SList(j, 1) = MList(i, 1) And SList(j, 2) = MList(i, 2) Then
.Rows(j).EntireRow.ClearContents
GoTo Nexti
end if
Next j
Nexti:
Next i

For r = Ubound(SList(1)) to 1 Step - 1
If .Cells(r, "A") = "" Then .Rows(r).Delete
Next r

End With 'ws
end if
Next ws

.Save
.Close
End With 'Wb

'End of Dir Loop here

[1819]

Hello 1819,

Help me out here. Is the column "A" data compared to every other column with data on the sheet or is column "A" of the master sheet compared to column "A" of the other sheets?

If the sheet names are all different how do I know which sheet will be the master?


Column "A" of the master sheet is compared to column "A" of the other sheets.

If the sheet names are all different how do I know which sheet will be the master? We can call it Master.

Thanks.

[1819]

Forgive me, but I do understand from this: How are the workbooks to be opened selected?

Thanks.

[1819]

Dim Wb As Workbook 'To be opened in Dir Loop
Dim MSht As Worksheet
Dim MShtName As String
Dim ws As Worksheet

Dim MList as Variant
Dim SList As Variant

Dim i as long
Dim j As Long
Dim r as Long


MShtName = "Master Worksheet Name Here"

'Start Dir loop to open workkbooks heere

With Wb
Set Msht = .Sheets(MshtName)

With MSht
Mlist = .Range(Range("A1"), .Cells(Rows.Count,"B").End(xlUp)).Value
End With

For Each ws in .WorkSheets
If not ws Is Msht then

With ws
SList = .Range(Range("A1"), .Cells(Rows.Count,"B").End(xlUp)).Value

For i = Lbound(Mlist(1)) to Ubound(MList(1))
For j = Lbound(Slist(1)) to Ubound(SList(1))
If SList(j, 1) = MList(i, 1) And SList(j, 2) = MList(i, 2) Then
.Rows(j).EntireRow.ClearContents
GoTo Nexti
end if
Next j
Nexti:
Next i

For r = Ubound(SList(1)) to 1 Step - 1
If .Cells(r, "A") = "" Then .Rows(r).Delete
Next r

End With 'ws
end if
Next ws

.Save
.Close
End With 'Wb

'End of Dir Loop here


Forgive me, but I do understand from this: How does this select which workbooks are to be opened? I am looking to open all workbooks in a particular folder.

Thanks.

[Aussiebear]

Hi 1819, Leith quite rightly asks you for the name of the "Master" Sheet, by that he needs to know which sheet is the one that all other sheets are to be compared against.

Is it for example Sheet 1 of every workbook?
Does this sheet have a name other than Sheet 1?
Are you only comparing 2 columns, on every sheet, in every workbook?

[1819]

Hi 1819, Leith quite rightly asks you for the name of the "Master" Sheet, by that he needs to know which sheet is the one that all other sheets are to be compared against.

Is it for example Sheet 1 of every workbook?
Does this sheet have a name other than Sheet 1?
Are you only comparing 2 columns, on every sheet, in every workbook?

I'm sorry, I misunderstood.

There is a single "Master" sheet sitting in one workbook. It will be the only sheet in that workbook.

The contents of "Master" need to be compared to all the sheets in all the workbooks in a given folder.

The names of those sheets will all be different. There will be multiple sheets in each workbook.

But the column names in each worksheet will be the same as each other and the same as in "Master".

I only need to compare one column in Master will the same column in all the other worksheets.

Thanks for your help.

[snb]

show a sample of your 'master', show a sample of a file that has to be compared to the master & please tell us why you want/have to do this to what purpose.

It looks as if you have a task at hand for which you still have no idea how to manage it. That's where the overall goal comes in.
The more specific your informatiion (what is in the different workbooks) the more specific a suggeestion can be.