Returns list of rows numbers having cells matching text in a column.
Passing column name, workbook, sheet and separator to return list in string.
Similar to CreateList_Matching.
Function RowList_Matching(SearchFor, InColumn, Optional Wb = "This", Optional Shee = "Active", Optional Sepa = "|", Optional StartFromRow = 1)
' Create list of row numbers in column where SearchFor is found
If Wb = "This" Then Wb = ThisWorkbook.Name
If Wb = "Active" Then Wb = ActiveWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(Wb).Worksheets(1).Name
Rett = ""
RowList_Matching = Rett
LastRow = Range("A1").EntireColumn.Rows.Count
If InColumn = "" Then InColumn = "A"
If SearchFor = "" Then Exit Function
DoEvents
Err.Clear
On Error Resume Next
I1 = StartFromRow
I2 = Workbooks(Wb).Worksheets(Shee).UsedRange.Rows.Count
' I2 = WorksheetFunction.CountA(Workbooks(Wb).Worksheets(Shee).Range(InColumn & 1).EntireColumn)
Rett = ""
Do Until I1 > I2 + 1
NextRow = WorksheetFunction.Match(SearchFor, Workbooks(Wb).Worksheets(Shee).Range(InColumn & I1, InColumn & LastRow), 0)
If Err.Number < > 0 Then Exit Do
I1 = I1 + NextRow - 1
If Rett > "" Then Rett = Rett & Sepa
Rett = Rett & I1
NextI1:
I1 = I1 + 1
DoEvents
Loop
On Error GoTo 0
Err.Clear
RowList_Matching = Rett
End Function
' Create list of row numbers in column where SearchFor is found
If Wb = "This" Then Wb = ThisWorkbook.Name
If Wb = "Active" Then Wb = ActiveWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(Wb).Worksheets(1).Name
Rett = ""
RowList_Matching = Rett
LastRow = Range("A1").EntireColumn.Rows.Count
If InColumn = "" Then InColumn = "A"
If SearchFor = "" Then Exit Function
DoEvents
Err.Clear
On Error Resume Next
I1 = StartFromRow
I2 = Workbooks(Wb).Worksheets(Shee).UsedRange.Rows.Count
' I2 = WorksheetFunction.CountA(Workbooks(Wb).Worksheets(Shee).Range(InColumn & 1).EntireColumn)
Rett = ""
Do Until I1 > I2 + 1
NextRow = WorksheetFunction.Match(SearchFor, Workbooks(Wb).Worksheets(Shee).Range(InColumn & I1, InColumn & LastRow), 0)
If Err.Number < > 0 Then Exit Do
I1 = I1 + NextRow - 1
If Rett > "" Then Rett = Rett & Sepa
Rett = Rett & I1
NextI1:
I1 = I1 + 1
DoEvents
Loop
On Error GoTo 0
Err.Clear
RowList_Matching = Rett
End Function
SearchFor, InColumn, Optional Wb = "This", Optional Shee = "Active", Optional Sepa = "|", Optional StartFromRow = 1
Views 66
Downloads 38
CodeID
DB ID
ANmarAmdeen
610
Revisions
v1.0
Monday
December
27
2021