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

0 Answers