How to filter and delete rows in excel vba

How to filter and delete rows in excel vba

Note -: Here we will discuss How to filter and delete rows in excel vba with log and error handling

On Error Resume Next
'vMasterFilePath=WScript.Arguments(0)
'vLogPath=WScript.Arguments(1)

vMasterFilePath="C:\Users\Dell\Desktop\Anil Singh\BPTestExcel.xlsx"
vLogPath="C:\Users\Dell\Desktop\Anil Singh\Log.txt"

Set objFS = CreateObject("Scripting.FileSystemObject")
Set FSO_Handle = objFS.OpenTextFile(vLogPath,8,True)

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True

Set objWorkbook1 =objExcel.Workbooks.Open(vMasterFilePath)

'Set objWorkbook1 =objExcel.Workbooks.Open(vMasterFilePath)

FSO_Handle.WriteLine(Now & "=> Excel Operation -:Input File(Master File)  has been opened successfully")
Dim RowCount
objWorkbook1.Worksheets("Sheet1").Activate
FSO_Handle.WriteLine(Now & "=> Excel Operation -: Sheet1 has been activated")
RowCount=objWorkbook1.Worksheets("Sheet1").UsedRange.Rows.Count
AutoCloseMsgBox(RowCount)
'MsgBox(RowCount)
FSO_Handle.WriteLine(Now & "=> Excel Operation -: Total Row in ExcelFile is"& RowCount)

Set rangDataSearch = objExcel.ActiveSheet.Range("D1:D"&RowCount)
Set rangData = objExcel.ActiveSheet.Range("A1:D"&RowCount)
firstAddress = Empty 

With rangDataSearch
 Set c = .Find("Java")
 If Not c Is Nothing Then
 firstAddress = c.Address
 End If
End With
MsgBox(firstAddress)

If firstAddress <> Empty Then
FSO_Handle.WriteLine(Now & "=> Excel Operation -: First Address of Filter Data is " & firstAddress)
With rangData
 .AutoFilter 4, "Java"
 .Select
 'Delete the visible rows while keeping the header
 .Offset(1,0).Resize(.Rows.Count-1).SpecialCells(12).Rows.EntireRow.Delete
 '.Resize(.Rows.Count-1).SpecialCells(12).Rows.EntireRow.Delete
End With

 With objExcel.ActiveSheet
 .AutoFilterMode = False
 If .FilterMode = True Then
 .ShowAllData
 End If
 End With
 FSO_Handle.WriteLine(Now & "=> Excel Operation -: Data has been deletd successfully")
  MsgBox("Data has been deletd successfully")
 Else
 FSO_Handle.WriteLine(Now & "=> Excel Operation -: Data is Not Found because Filter Data first Address value is blank ")
 MsgBox("Data is Not Found")
 End If
 objWorkbook1.Save
 objWorkbook1.Close True
 objExcel.Quit
AutoCloseMsgBox("Excel Operation Activity has been completed successfully")
FSO_Handle.WriteLine(Now & " => Excel Operation-: Activity has been completed successfully")

If Err.Number <> 0 Then
FSO_Handle.WriteLine(Now & " | Excel Operation | Error | " & Err.Number &"-:" & Err.Description)
AutoCloseMsgBox("Error")
WScript.StdOut.WriteLine "fail"
Else
WScript.StdOut.WriteLine "pass"
AutoCloseMsgBox("No  Error")
End If 

'write a function For auto close message box
Function AutoCloseMsgBox(msg)
CreateObject("Wscript.Shell").Popup msg,1,"Auto Close",4+32
End Function

Log-:

Condition-1  (Data is available  in  excel)

4/18/2018 6:12:23 AM=> Excel Operation -:Input File(Master File)  has been opened successfully

4/18/2018 6:12:23 AM=> Excel Operation -: Sheet1 has been activated

4/18/2018 6:12:24 AM=> Excel Operation -: Total Row in ExcelFile is10

4/18/2018 6:12:27 AM=> Excel Operation -: First Address of Filter Data is $D$3

4/18/2018 6:12:27 AM=> Excel Operation -: Data has been deletd successfully

4/18/2018 6:12:30 AM=> Excel Operation-: Activity has been completed successfully

4/18/2018 6:25:48 AM=> Excel Operation -:Input File(Master File)  has been opened successfully

Condition-2  (Data is not available  in  excel)

4/18/2018 6:25:48 AM=> Excel Operation -: Sheet1 has been activated

4/18/2018 6:25:49 AM=> Excel Operation -: Total Row in ExcelFile is7

4/18/2018 6:25:54 AM=> Excel Operation -: Data is Not Found because Filter Data first Address value is blank

4/18/2018 6:25:57 AM => Excel Operation-: Activity has been completed successfully

Condition-3  (Error Handling)

4/18/2018 6:26:26 AM=> Excel Operation -:Input File(Master File)  has been opened successfully

4/18/2018 6:26:26 AM=> Excel Operation -: Sheet1 has been activated

4/18/2018 6:26:27 AM=> Excel Operation -: Total Row in ExcelFile is

4/18/2018 6:26:30 AM=> Excel Operation -: Data is Not Found because Filter Data first Address value is blank

4/18/2018 6:26:33 AM => Excel Operation-: Activity has been completed successfully

4/18/2018 6:26:33 AM | Excel Operation | Error | 424-:Object required