Getting row index for particular given string value of string

Hi Folks,

Sometimes, you need to find the row index of particular row in sheet based on fixed value string and need to operate some operation based on that row like adding and delete row above and below that string value row, this code might help you.

strSearchTxt ="This is search row text for index find of this row"

Dim iTextRow As Long: iTextRow = GetTextRow(strSearchTxt )

Public Function GetTextRow(strSearchTxt)

GetTextRow = 0

With ActiveSheet

    Set Rng = .UsedRange.Find(strSearchTxt)

    If Not (Rng Is Nothing) Then GetTextRow = Rng.Row

End With

End Function

Cheers.

Get Column index based on column name using vba

Hi folks,

If you need to find the column index based on column name, when your column sequence is not fixed in your sheet, please find below code which might helpful to you.

Code sample 1:
 Set findrng = wST.Range("1:1")
 ColNameIndex = findrng.Find("searchcolname").Column

Code sample 2:
strnameindex = wST.Match("searchcolname",wST.Range("1:1"), 0)

Code sample 3:
Function GetHeaderColumn(shRead As Worksheet, header As String) As Integer
    Dim headers As Range
    Set headers = shRead.Range("1:1")
    GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)
End Function

Cheers.
TJ.