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

boddulus · Sep 4, 2014

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

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....:happy:

Please share your results after testing on my sample sheets.

WizzardOfOz · Sep 9, 2014

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

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

boddulus

New member

Attachments

WizzardOfOz

New member