Macro Excel - CleanData

Hello,

I have 6 specific sheets that i like to delete unwanted rows : IN,PR,WO,HOS,HOH,CH.   I have a macro, but i am not to sure how to use an array with it in order to call my sheets , Can you help?

************************************:

Sub DeleteNonPCObjects()
Dim ws As Worksheet
Dim lr As Long, i As Long
Application.ScreenUpdating = False
Sheets("IN").Select
    lr = Sheets("IN").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = lr To 1 Step -1
        If WorksheetFunction.CountIf(Sheets("IN").Rows(i), "Server") > 0 Then
            Sheets("IN").Rows(i).Delete
        End If
        If WorksheetFunction.CountIf(Sheets("IN").Rows(i), "Linux") > 0 Then
            Sheets("IN").Rows(i).Delete
        End If
        If WorksheetFunction.CountIf(Sheets("IN").Rows(i), "Unix") > 0 Then
            Sheets("IN").Rows(i).Delete
        End If
    Next i
Application.ScreenUpdating = True
End Sub
mldaigle1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Attach an example workbook with some dummy data in. I would think that automating AutoFilter would be the most efficient way. Here's some sample code that could be adapted


Option Explicit

Sub deleteFilteredData()
    Dim rDelete    As Range
    Dim lCalc  As Long
    Dim sDelete As String
     
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
 On Error GoTo exit_proc
     
     'Obtain the value that you want to delete
    sDelete = Application.InputBox("Enter data  for deletion")
    If sDelete = Empty Then Exit Sub 'user cancelled
     
     'Sheet with the data, change the name
    With Sheet1
        .AutoFilterMode = False
         
         'Apply the filter, this range of data starts in A1
        .Cells(1, 1).CurrentRegion.AutoFilter Field:=1, Criteria1:=sDelete
         
        With .AutoFilter.Range
            On Error Resume Next
            Set rDelete = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
            .SpecialCells(xlCellTypeVisible)
'            On Error GoTo 0
            If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
        End With
         'Remove the AutoFilter
        .AutoFilterMode = False
    End With
     
exit_proc:
        .ScreenUpdating = True
        .Calculation = lCalc
    End With
     
End Sub

Open in new window

0
NorieVBA ExpertCommented:
Perhaps.
Sub DeleteNonPCObjects()
Dim ws As Worksheet
Dim lr As Long, I As Long, J As Long
Dim arr

    Application.ScreenUpdating = False

    arr = Array("IN", "PR", "WO", "HOS", "HOH", "CH")

    For J = LBound(arr) To UBound(arr)

        With Sheets(arr(J))
            lr = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For I = lr To 1 Step -1
                If WorksheetFunction.CountIf(.Rows(I), "Server") > 0 Then
                    .Rows(I).Delete
                End If
                If WorksheetFunction.CountIf(.Rows(I), "Linux") > 0 Then
                    .Rows(I).Delete
                End If
                If WorksheetFunction.CountIf(.Rows(I), "Unix") > 0 Then
                    .Rows(I).Delete
                End If
            Next I
        End With

    Next J

    Application.ScreenUpdating = True
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mldaigle1Author Commented:
Wow!! this was a quick answer and efficient solution!!

Big thanks Norie!!

kiss

//mld
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

mldaigle1Author Commented:
Roy,

Sorry, I look right away at the end of the message and missed your answer.  

:-/
0
Roy CoxGroup Finance ManagerCommented:
With large amounts of data AutoFilter is considerably faster
0
NorieVBA ExpertCommented:
I totally agree with Roy looping is kind of a brute force method and for large amount of data is going to be time consuming.

Using filters, or other methods that can delete en masse is far more efficient.

It would be easy to combine what Roy and I posted.
Option Explicit

Sub DeleteNonPCObjects()
Dim J As Long
Dim arr
Dim rDelete As Range
Dim lCalc As Long
Dim sDelete As String

    arr = Array("IN", "PR", "WO", "HOS", "HOH", "CH")

    With Application
    
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        On Error GoTo exit_proc
        sDelete = Application.InputBox("Enter data  for deletion")
        If sDelete = Empty Then Exit Sub    'user cancelled

        For J = LBound(arr) To UBound(arr)

            With Sheets(arr(J))
                .AutoFilterMode = False

                'Apply the filter, this range of data starts in A1
                .Cells(1, 1).CurrentRegion.AutoFilter Field:=1, Criteria1:=sDelete

                With .AutoFilter.Range
                    On Error Resume Next
                    Set rDelete = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                                  .SpecialCells(xlCellTypeVisible)
                    '            On Error GoTo 0
                    If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
                End With
                'Remove the AutoFilter
                .AutoFilterMode = False
                
            End With

        Next J

exit_proc:
        .ScreenUpdating = True
        .Calculation = lCalc
        
    End With

End Sub

Open in new window

0
mldaigle1Author Commented:
i tried the new macro,  put in a keyword via the input box (but i have a list of keywords, "WAPPR", "REJECTED", CANCELED").  

The macro worked fine for the keyword then did not process the other sheet.



I attach a sample as well, sorry, i am not fast today.

:)
Data_Dump.xlsm
0
NorieVBA ExpertCommented:
I just cobbled the last code together and didn't properly look at Roy's original code.

I think what's needed is to introduce another array for the terms, eg Linux, Unix etc, to search for.

I'll try and post something for that later.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.