Results 1 to 2 of 2

Thread: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

  1. #1

    Question Can some one please help me with V.B.A code to remove the unwanted data in the sheet.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi folks Good morning, Good afternoon or Good evening to all of you......

    I am new to VBA coding, i have been assigned a project where i have to clean up the data and do valuation on it. but the data size is every huge like i have around 2000 rows in each sheet i have 15 sheet with similar pattern of data but with slight changes in each sheet.
    1) I want all the blank rows and all the rows with unwanted data (like R1 to R6) like security,secu,-----,uds, all of them should get filtered and get deleted,but "before deleting also excel should ask whether it is ok to delete this data or not and when i click on ok it should get deleted"
    2) I want all the rows with value equal to 0.00001 in column 7 and column 8 should get filtered out and get deleted,but "before deleting also excel should ask whether it is ok to delete this data or not and when i click on ok it should get deleted"
    3) I want all the rows with values which starts with "UBS" in column 4 should get filtered out and get deleted,but "before deleting also excel should ask whether it is ok to delete this data or not and when i click on ok it should get deleted".
    i want this all this happen in sequence and apart from above conditions rows i don't want to miss even one row please.
    Note : there is a slightest difference in sheet 1 and sheet 2 "UBS" is not there in sheet 2 ,please frame a code for all sheets in such a conditions that the macro doesn't get struck if one of the above conditions are not there in any of the sheet.
    I have attached the workbook with sample sheets (Sheet 1 and sheet 2) . the data is huge(around 2000 rows) and sheets are around 15 with 1 or 2 or all above conditions in the sheet.
    Thanks for your time and effort. your work will be much appreciated......... thanks in advance....
    Please share your results after testing on my sample sheets.
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Create a module, suggest either as a personal module, xlam or worst case in the file itself.

    There are three functions

    Sortit, does the grunt work and adds a row number but more importantly flags it as good/bad data
    DeleteIt, does the deleting
    CleanupColumns, to get rid of the two added columns

    Typicaly run Sortit once
    Then Run Deleteit, if there is something you don't wish to delete select NO, change the row to keep and rerun the Deleteit
    Finally cleanup the columns.

    Module below
    ____________________________
    Code:
    Option Explicit
    
    
    Sub SortIt()
    Dim oSheet, oData, oRange
    Dim lrow As Long, lcolumn As Long, i As Long, j As Long
    
    
        For Each oSheet In ActiveWorkbook.Sheets
            
            'read the data in
            lrow = oSheet.UsedRange.Rows.Count
            lcolumn = oSheet.UsedRange.Columns.Count + 2            'add rowid and keep/delete
            If lcolumn > 8 Then     'is it empty
            
                oData = oSheet.Range("a1").Resize(lrow, lcolumn).Value2
                
                'flag it as good/bad
                For i = 1 To lrow
                    oData(i, lcolumn - 1) = i 'the row counter
                    If IsEmpty(oData(i, 7)) Or IsEmpty(oData(i, 8)) Then
                        oData(i, lcolumn) = "Del_Empty"
                    Else
                    
                        If IsNumeric(oData(i, 7)) And IsNumeric(oData(i, 8)) Then
                            If oData(i, 7) <> 0.00001 And oData(i, 8) <> 0.00001 Then
                                oData(i, lcolumn) = IIf(InStr(oData(i, 4), "UBS") > 0, "Del_UBS", "Keep")
                            Else
                                oData(i, lcolumn) = "Del_0.00001"
                            End If
                        Else
                            oData(i, lcolumn) = "Del_non Numeric"
                        End If
                    End If
                Next i
                
                'dump it out and sort
                oSheet.Cells.Clear
                
                Set oRange = oSheet.Range("a1").Resize(lrow, lcolumn)
                With oRange
                    .Value2 = oData
                    .Sort key1:=.Cells(1, lcolumn), order1:=xlDescending, _
                        key2:=.Cells(1, lcolumn - 1), order2:=xlAscending, _
                        Header:=xlNo
                End With
            End If
        Next
    End Sub
    
    
    Sub DeleteIt()
        Dim aStrings, aString, oRange, oSheet
        Dim lrow As Long, lcolumn As Long
        
        aStrings = Array("Del_Empty", "Del_non Numeric", "Del_0.00001", "Del_UBS")
        
        For Each oSheet In ActiveWorkbook.Sheets
            oSheet.Select
        
            lrow = oSheet.UsedRange.Rows.Count
            lcolumn = oSheet.UsedRange.Columns.Count
            Set oRange = oSheet.Range("a1").Resize(lrow, lcolumn)
            
            For Each aString In aStrings
                With oSheet
                    .AutoFilterMode = False
                    With oRange
                        .AutoFilter lcolumn, aString
                        On Error Resume Next
                        If MsgBox("Rows containing " & aString & " to be deleted" & vbCrLf & vbCrLf & _
                            "Do you want to delete these rows", vbCritical + vbYesNo, _
                            "Confirm Deletion") = vbYes _
                                Then .Offset(1).SpecialCells(12).EntireRow.Delete
                        On Error GoTo 0
                    End With
                    .AutoFilterMode = False
                End With
            Next
        Next
    End Sub
    
    
    Sub CleanupColumns()
        Dim oSheet
        For Each oSheet In ActiveWorkbook.Sheets
            oSheet.Select
            oSheet.Range(Cells(1, 12), Cells(1, 13)).EntireColumn.Delete
        Next
    End Sub
    Last edited by p45cal; 2016-03-02 at 11:26 PM. Reason: added code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •