Find A Particular Text Within Ranges And Return Its Row Position

In Microsoft Excel, sometimes we need to find the row position of particular text or even formula, to be able to insert certain of data below the corresponding text or even to delete row that contain the corresponding text.

This is an excel vba macro code to return row position that contain specific text.

Function rowPosition(ByVal searchText As Variant, _
    Optional ByVal stLookAt As XlLookAt = xlPart) As Long
    
    Dim rPos As Long, rS As Range
    
    rPos = 0
    
    Set rS = Cells.Find(searchText, LookIn:=xlValues, LookAt:=stLookAt)
    
    If Not rS Is Nothing Then rPos = rS.Row
    
    rowPosition = rPos
    
End Function

The function above have two arguments:

searchText : the text you are searching for
stLookAt : use xlWhole if you want to search the whole text, or use xlPart if otherwise

Here is example how to use the function above. This example deletes entire row that contain word "Test".

Sub RunExample()
    Dim rwPos As Long
       
    'Find row position
    rwPos = rowPosition("Test", xlPart)
    
    'If found then delete entire row
    If rwPos > 0 Then Rows(rwPos).Delete
End Sub

Thank you for reading this article Find A Particular Text Within Ranges And Return Its Row Position With URL https://x-tutorials.blogspot.com/2009/02/find-particular-text-within-ranges-and.html. Also a time to read the other articles.