Solved

Excel VBA

Posted on 2014-02-23
7
368 Views
Last Modified: 2014-02-23
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(xlUp).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
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 39880850
What other sheets do you want to compare?
0
 

Author Comment

by:W.E.B
ID: 39880856
Hello,
COM212
COM216
COM222

thanks
0
 
LVL 34

Expert Comment

by:Norie
ID: 39880862
Do you want to compare them to COM1 or to each other?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:W.E.B
ID: 39880866
to COM1
0
 
LVL 34

Accepted Solution

by:
Norie earned 300 total points
ID: 39880976
Perhaps.
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")
    For Each ws1 In ActiveWorkbook.Worksheets

        If ws1.Name Like "COM*" And Not ws1 Is ws Then


            xLastRow = ws1.Range("A5000").End(xlUp).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 If
    Next ws1
    
End Sub

Open in new window

0
 

Author Comment

by:W.E.B
ID: 39881138
Hello,
thank you , this is working,

But,
how do i exclude some sheets that start with COM ?

example,
COM123
COM321

thanks
0
 

Author Closing Comment

by:W.E.B
ID: 39881190
Got it,
Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question