Excel VBA: 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

Related posts:

• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)