I am new to vbscript and I am creating a script to reduce my day by day duplicate efforts. I have a column in an excel sheet which holds values. There are certain cells under this column where multiple values exists. Now I have an array which has some values which needs to be looked up / searched within each row under this column and delete the row if array values are not present within the row.
I tried to search the array values in the rows using InStr function and it worked if cell contains only one value. Code is attached below.
This sub is not working as expected if cell contains multiple values. e.g. Project 1 [ALT + ENTER] Dummy Project
Hence I tried to use Find and Search methods. I am unable to get the expected results using these methods too.
Fix Version/s Row 1 - Project 3 a Row 2 - Project 2 'spaces at the end Row 3 - Project 4 --------- Project 1 Row 4 - Project 5 Row 5 - Project 1
Find method - No rows deleted Replace method - Getting syntax error where I used Search method in place of InStr function as below,
If objWorksheet1.Cells(iDelCnt, fixVerColNum).Search(objWorksheet1.Cells(iDelCnt, fixVerColNum).Value, projectFilter(jDelCnt)) <> 0 Then
Please assist. Thanks in advance.
Expected - I expect row 'x' to be deleted if array value doesn't exists in Cells(x,y)
Dim objExcel, objWorkbook, objWorksheet1 Dim iDelCnt, jDelCnt, projectFilter, lRow, fixVerColNum, tempCell, deleteCounter, InStrTest Set objExcel = CreateObject("Excel.Application") objExcel.Application.Visible = True Set objWorkbook = objExcel.Workbooks.Open(filePath) ' Location of the file on drive Set objWorksheet1 = objWorkbook.Worksheets(1) projectFilter = Array("Project 1","Project 2", "Project 3") fixVerColNum = objExcel.Match("Fix Version/s", objWorksheet1.Range("A1:T1"), 0) 'Identify "Fix Version(s)" column number lRow = objWorksheet1.Range("A1").CurrentRegion.Rows.Count deleteCounter = 0 For iDelCnt = lRow to 2 Step -1 For jDelCnt = LBound(projectFilter) to UBound(projectFilter) If InStr(1, objWorksheet1.Cells(iDelCnt, fixVerColNum).Value, projectFilter(jDelCnt), 1) <> 0 Then deleteCounter = 1 Exit For Else deleteCounter = 0 End If Next If deleteCounter = 0 Then objWorksheet1.Rows(iDelCnt).EntireRow.Delete End If Next