Create Dynamic Range Searching with Excel VBA

The goal is to find a specific value within a dynamic range of data. The range could change based on the number of rows or columns in your worksheet, and this example will adjust the range dynamically as data is added or removed.

VBA Code:

Sub CreateDynamicRangeSearch()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim searchValue As Variant
    Dim foundCell As Range
    Dim lastRow As Long
    Dim lastCol As Long
    ' Set the worksheet to work with
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change Sheet1 to your actual sheet name
    ' Input the value you are searching for
    searchValue = InputBox("Enter the value to search for:", "Search Value")
    ' Find the last row and column of the used range in the sheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Define the dynamic search range
    Set searchRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))  
    ' Search for the value in the dynamic range
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
    ' Check if the value was found
    If Not foundCell Is Nothing Then
        MsgBox "Value found in cell: " & foundCell.Address, vbInformation, "Search Result"
    Else
        MsgBox "Value not found.", vbExclamation, "Search Result"
    End If
End Sub

Explanation:

1. Define Variables:
   • ws: This represents the worksheet where the data is located (you can change the sheet name).
   • searchRange: This will be the dynamic range in which you will search for the value.
   • searchValue: The value the user is looking for, which is entered through an InputBox.
   • foundCell: This will hold the cell where the search value is found.
   • lastRow and lastCol: These variables are used to find the last row and column with data in the sheet.
2. Worksheet Setup:
   • The code uses Set ws = ThisWorkbook.Sheets(« Sheet1 ») to define the worksheet. Make sure to replace « Sheet1 » with the name of your actual sheet.
3. Get Last Row and Column:
   • lastRow is calculated by finding the last non-empty row in column 1 (usually column A).
   • lastCol is calculated by finding the last non-empty column in row 1 (the first row).
4. Dynamic Range Definition:
   • The range for the search is defined from cell (1,1) (top-left of the sheet) to the last row and column determined above. This creates a dynamic range based on the data present in the sheet.
5. Search Process:
   • The Find method is used to search within the searchRange. The What:=searchValue tells Excel what to search for, LookIn:=xlValues ensures that the search looks at the cell contents, and LookAt:=xlWhole ensures that the entire content of the cell is matched.
6. Result Handling:
   • If the value is found, a message box will display the cell address where the value was located.
   • If the value is not found, a message box will notify the user.

Output:

When you run the code, an input box will appear, asking you to enter a search value. After entering the value, the code will search through the dynamic range and display a message box indicating either the cell address where the value was found or informing you that the value was not found.