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
