Excel - Best way to locate cell next cell that does not contain a specific value

[stefan.hansen]

Hey!

So I am currently working on a process where I have to write a LOT of data from a large excel file, like 16K lines.
I have sorted the whole file and now I need to locate all lines in a specific column that contains a certain value, in this case "104". I know for sure that we will have lines with 104, but all other values is not known from time to time. So I need a way to locate the start row and end row of the rows with 104 in one column.

My thinking is to got to the first cell in that column, get the value, check if it's 104, if not use this code stage to go to next cell that is not that value and check this value. Then keep on doing this until I have located the start and end row with 104.

I think this could be easy to do by using the "MS VBO Extended - CUSTOM_Find Next Cell With Text Value"

Input: handle, direction, value
Output: cellref

Dim strDir as String = direction.Trim().Substring(0,1).ToUpper()
Dim excel as Object = GetInstance(handle)
Dim dirn as Integer = 0


Select Case strDir
Case "U"
dirn = -4162 ' Excel.XlDirection.xlToUp
Case "D"
dirn = -4121 ' Excel.XlDirection.xlToDown
Case "L"
dirn = -4159 ' Excel.XlDirection.xlToLeft
Case "R"
dirn = -4161 ' Excel.XlDirection.xlToRight
Case Else
Throw New ArgumentException("Invalid Direction: " & strDir)
End Select

Dim cell as Object = excel.ActiveCell

While True

Dim nextCell as Object = GetNextCell(cell, strDir)

If cell.Address = nextCell.Address Then
cellref = ""
Return
ElseIf cstr(nextCell.Value) = value Then ' We've found our blank
cellref = nextCell.Address(False,False)
Return
End If

cell = nextCell


End While


Is anyone able to help modify this code stage to find the next cell that does NOT contain the value?

------------------------------
Stefan Hansen
RPA Dev
Bestseller
Europe/Copenhagen
------------------------------

[ewilson]

@stefan.hansen,

From the looks of it, your code should match whatever you pass in to it as value.  So, if you set the active cell of the worksheet to something at the top (ex. cell A1) and call this code with direction set to D then it will search​ the cells in column A going down and return the first one it reaches that matches your value. Then if you set the active cell of the worksheet to the last cell in column A and call this action again with the direction set to U it should search the column values going up from the bottom and return the first cell it matches. Assuming the range of values you're looking for are consecutive (i.e. no other values interspersed) you now have the first and last cell.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[devneetmohanty07]

" I have sorted the whole file and now I need to locate all lines in a specific column that contains a certain value, in this case "104"."

If you want to indicate all the lines having any specific value in a collection, in that case I have modified the code in such a way that you need to provide the following inputs & outputs:

Inputs:

• handle : The excel instance handler
• direction: The cell direction which can be either, "L","R","D" or "U"
• cellref: The cell reference from where you need to start the comparison
• workbookname: The name of the excel workbook
• worksheetname: The name of the excel worksheet
• value: The value to be matched

Outputs:

• lines: A dynamic collection holding no values initially. After code execution, line numbers consisting of that specific value will be stored in a single column called "lineNo"

Dim strDir as String = direction.Trim().Substring(0,1).ToUpper()
Dim excel as Object = GetInstance(handle)
Dim dirn as Integer = 0

Select Case strDir
Case "U"
dirn = -4162 ' Excel.XlDirection.xlToUp
Case "D"
dirn = -4121 ' Excel.XlDirection.xlToDown
Case "L"
dirn = -4159 ' Excel.XlDirection.xlToLeft
Case "R"
dirn = -4161 ' Excel.XlDirection.xlToRight
Case Else
Throw New ArgumentException("Invalid Direction: " & strDir)
End Select

Dim totalRows As Integer = GetWorksheet(handle,workbookname,worksheetname).Cells.Find("*", , , , , 2).Row
Dim lineCount As Integer = 1
excel.ActiveSheet.Range(cellref,cellref).Activate()
Dim cell as Object = excel.ActiveCell
Dim temp As New DataTable
temp.Columns.Add("lineNo", GetType(Integer))

For i As Integer = 0 To totalRows

Dim nextCell as Object = GetNextCell(cell, strDir)

If nextCell.Value = value Then ' We've found our value
temp.Rows.Add(lineCount)
End If

cell = nextCell
lineCount +=1

Next
lines=temp


In case you require only start and end row, the collection initial and end row can be also taken as per your requirement.

------------------------------
Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
------------------------------

[stefan.hansen]

Of cause! I feel stupid I didn't just do that. Thank for the help. Sometimes you just need another set of eyes on something to see it clearly.

------------------------------
Stefan Hansen
RPA Dev
Bestseller
Europe/Copenhagen
------------------------------

[stefan.hansen]

Thanks! I'll check it out and test it. seems like a great solution.

------------------------------
Stefan Hansen
RPA Dev
Bestseller
Europe/Copenhagen
------------------------------

[michaeloneil]

Hi Stefan

For 14k+ rows going through each one and identifying the individual cell references may take a long time and slow your process down. I've had a similar issue before and what I did was to turn on filters and filter the columns with the value I was looking for and then use find last empty cell to identify how many rows there are then I was able to take those rows into a collection. If you wanted to try something similar the code for turning filters on/off is below and also the code for filtering the columns.

Turn on/off filters - Input values handle, Source Worksheet, Source Worbook, Filter range 
----------------------------------------------------------------------------------------------
Dim wb, ws As Object
Dim excel, sheet, range As Object

wb = GetWorkbook(handle, Source_Workbook)
ws = GetWorksheet(handle, Source_Workbook, Source_Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Filter_range)
range.Select()

excel.Selection.AutoFilter

================================================

Filter columns - Inputs handle, range, workbookname, worksheetname, columnnumber, criteriastring. Outputs - success, message
-----------------------------------------------------------------------------------------------------------------------------------------
Dim ws as Object, strList as Object

ws = GetWorksheet(handle, workbookname, worksheetname)

Try

If (criteriastring<>"") Then
strList = Split(criteriastring,",",-1)
'ColNum = ws.Range(range).Find(columnname).Column
ws.Range(range).AutoFilter(Field:=columnnumber, Criteria1:= strList, Operator:=7)
success = True
Else
success = False
message = "CriteriaString should not be blank"

End If

Catch ex as exception

success = False
message = ex.Message

End Try

Hope this helps

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[krishna.kodimurali9]

I believe we can filter on that column with criteria as "Contains"  and then get the row count.

------------------------------
MuraliKrishna
Senior Consultant - Automation Developer
------------------------------