W.E.B
asked on
Excel VBA
Hello,
can you please help,
I'm using below code to compare sheets Columns and then Delete rows.
right now, I run it one sheet at a time,
can I run this for multiple sheets.
Example
Set ws1 = Sheets("COM116")
I have sheets
COM58, COM116, COM170,COM135, COM150, COM235 , ..... and more..
Sub TESTTTT()
Sub Compare_Delete()
Dim i As Long
Dim iLastRow As Long, xLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("COM1")
Set ws1 = Sheets("COM116")
'ws1.Visible = xlSheetVisible
xLastRow = ws1.Range("A5000").End(xlU p).Row
With ws
iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = iLastRow To 2 Step -1
For x = xLastRow To 2 Step -1
If .Cells(i, "A").Value = ws1.Cells(x, "A").Value And _
.Cells(i, "B").Value = ws1.Cells(x, "B").Value And _
.Cells(i, "C").Value = ws1.Cells(x, "C").Value And _
.Cells(i, "W").Value = ws1.Cells(x, "W").Value Then
.Rows(i).Delete
End If
Next x
Next i
End With
End Sub
Any help is appreciated.
thanks
can you please help,
I'm using below code to compare sheets Columns and then Delete rows.
right now, I run it one sheet at a time,
can I run this for multiple sheets.
Example
Set ws1 = Sheets("COM116")
I have sheets
COM58, COM116, COM170,COM135, COM150, COM235 , ..... and more..
Sub TESTTTT()
Sub Compare_Delete()
Dim i As Long
Dim iLastRow As Long, xLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("COM1")
Set ws1 = Sheets("COM116")
'ws1.Visible = xlSheetVisible
xLastRow = ws1.Range("A5000").End(xlU
With ws
iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = iLastRow To 2 Step -1
For x = xLastRow To 2 Step -1
If .Cells(i, "A").Value = ws1.Cells(x, "A").Value And _
.Cells(i, "B").Value = ws1.Cells(x, "B").Value And _
.Cells(i, "C").Value = ws1.Cells(x, "C").Value And _
.Cells(i, "W").Value = ws1.Cells(x, "W").Value Then
.Rows(i).Delete
End If
Next x
Next i
End With
End Sub
Any help is appreciated.
thanks
What other sheets do you want to compare?
ASKER
Hello,
COM212
COM216
COM222
thanks
COM212
COM216
COM222
thanks
Do you want to compare them to COM1 or to each other?
ASKER
to COM1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello,
thank you , this is working,
But,
how do i exclude some sheets that start with COM ?
example,
COM123
COM321
thanks
thank you , this is working,
But,
how do i exclude some sheets that start with COM ?
example,
COM123
COM321
thanks
ASKER
Got it,
Thanks
Thanks