Search worksheet and return all values in column directly below reference cell

[mattm1981]

Hi, is is possible for me to write a formula for excel to search a worksheet for a specific value(s) and return all values in the column directly below where it finds the result? The use case is to extract data from property tenancy schedules. The schedules are all different formats so I would like Excel to search all columns and if it finds 'Lease expiry date' in say G16, then it will return all values from G17 down to say G2000 and paste to another worksheet.
Thanks for the help

 

[Anbuselvam]

This will search both values, Also you can modify the search value as you want

Sub ExtractData()

    Dim searchValues As Variant
    Dim searchRange As Range
    Dim resultRange As Range
    Dim foundCell As Range
    Dim lastRow As Long
    Dim outputRow As Long
    
    'Set the search values to look for
    searchValues = Array("Lease expiry date", "Lease end date")
    
    'Set the search range to look for the search values
    Set searchRange = ActiveSheet.UsedRange
    
    'Find the first occurrence of any of the search values in the search range
    For Each searchValue In searchValues
        Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            Exit For
        End If
    Next searchValue
    
    'If any of the search values is found
    If Not foundCell Is Nothing Then
        'Set the result range to be the column directly below where the search value is found
        lastRow = ActiveSheet.Cells(Rows.Count, foundCell.Column).End(xlUp).Row
        Set resultRange = Range(foundCell.Offset(1, 0), Cells(lastRow, foundCell.Column))
        
        'Copy the result range to a new worksheet
        outputRow = 1
        For Each cell In resultRange
            Sheets("Output").Cells(outputRow, 1) = cell.Value
            outputRow = outputRow + 1
        Next cell
    End If
    
End Sub

 

[mattm1981]

Fantastic! Thanks so much for your help

 

[Anbuselvam]

Glad to know that you got an answer