VBA Find in Column in Excel (7 Approaches)

There are different methods to find a value or particular item in the column by using VBA. In this article, I’m going to explain different methods of VBA find in column in Excel.

To make this explanation clearer, I’m going to use a sample dataset. There are 4 columns in the dataset representing sales information. These columns are Sales Person, Region, Product Name, and Price.

Sample Dataset

Download to Practice

Methods to VBA Find Value in Column in Excel

1. Find Specific Value in Column

You can write VBA functions to find values in a column.
In this section, I’m going to write a function to find the value in a column in Excel.

First, open the Developer tab >> then select Visual Basic

Find Specific Value in Column using VBA

It will open a new window of Microsoft Visual Basic for Applications.
Now, from Insert >> select Module

 

A Module will be opened.
Then, write the following code in the Module to create the function.

Function FindValueInColumn(MyColumn As Range, MyValue As Variant) As String
    With MyColumn
        FindValueInColumn = .Find(What:=MyValue, After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With    
End Function

Find Specific Value in Column using VBA

Here, I named the function as FindValueInColumn
This function will accept two arguments; these are MyColumn and MyValue.

MyColumn is the column you search in.
MyValue is the numeric value you search for.

Also, used the Range.Find method within the With statement. In What parameter used MyValue which will find/search the value in.

After parameter will specify after which the search begins.

LookIn:=xlValues parameter will specify the type of data to search in and it also can take any of the built-in constants or values from the XlFindLookIn enumeration.

In the LookAt parameter provided xlWhole to look at the whole cell search contents.

SearchOrder:=xlByRows parameter will move horizontally and also loops up through each row until it finds a non-empty cell.

SearchDirection:=xlNext parameter will search for the next match.

Now, Save the code and go back to the worksheet. Then, select the cell where you want to place your resultant value and type the following formula in the selected cell or into the Formula Bar.

=FindValueInColumn(C4:C14,G4)

Here, in the created function FindValueInColumn taken the column range C4:C14 to find the value C4 (Malta).

Finally, press the ENTER key. Now, you will see the location of the given value which is C13.

Find Specific Value in Column using VBA

2. Find Value in Table Column

You can write VBA code to find values in the column from a formatted table in Excel. In this section, I’ll define a function to find value from a table column.

First things first, open the Developer tab >> then select Visual Basic

Find Value in Table Column using VBAIt will open a new window of Microsoft Visual Basic for Applications.
Now, from Insert >> select Module

A Module will be opened.
Then, write the following code to create the function.

Function FindValueInTableColumn(MyWorksheetName As String, MyColumnIndex As Long, _
MyValue As Variant, Optional MyTableIndex As Long = 1) As String
    With ThisWorkbook.Worksheets(MyWorksheetName).ListObjects(MyTableIndex).ListColumns(MyColumnIndex).DataBodyRange
        FindValueInTableColumn = .Find(What:=MyValue, After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
End Function

Find Value in Table Column using VBA

Here, I named the function as FindValueInTableColumn

This function will accept up to four arguments; these are MyWorksheetName, MyColumnIndex , MyValue and MyTableIndex (optional).

MyWorksheetName is the sheet from which you will search in.
MyColumnIndex is the column number from which you will search the value.
MyValue is the numeric value you search for.
MyTableIndex is the index number of the Excel table which is optional with a default value of 1. Either you can use it or avoid it.

Then, used the Range.Find method within the With statement.

After writing the code, Save the code and go back to the worksheet.

Now, select the cell where you want to place your resultant value.
Then, type the following formula in the selected cell or into the Formula Bar.

=FindValueInTableColumn("Table Column",1,G4)

Here, in the created function FindValueInTableColumn given the MyWorkSheetName which is  Table Column, next chosen the first column to find the value, Adam Smith.

Finally, press the ENTER key. Thus, you will find the location of the selected value which is B4.

Find Value in Table Column using VBA

3. Find String Value in Column

You can define your own functions to find strings in a column using VBA. Let’s write one.
To write a function to find the value in a column in Excel.

First, open the Developer tab >> then select Visual Basic

Find String Value in Column using VBA

It will open a new window of Microsoft Visual Basic for Applications.
Now, from Insert >> select Module

A Module will be opened.
Then, write the following code to create the function.

Function FindStringInColumn(MyColumn As Range, MyString As Variant) As String
    With MyColumn
        FindStringInColumn = .Find(What:=MyString, After:=.Cells(.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
End Function

Find String Value in Column using VBA

Here, I named the function as FindStringInColumn

This function will accept two arguments; these are MyColumn and MyString.

MyColumn is the column you search in.
MyString is the string or text you search for.

Then, used the Range.Find method within the With statement. In What parameter used MyValue which will find/search the value in.

After parameter will specify after which the search begins.

LookIn:=xlValues parameter will specify the type of data to search in and it also can take any of the built-in constants or values from the XlFindLookIn enumeration.

In the LookAt parameter provided xlWhole to look at the whole cell search contents.

SearchOrder:=xlByRows parameter will move horizontally and also loops up through each row until it finds a non-empty cell.

SearchDirection:=xlNext parameter will search for the next match.

MatchCase:=False parameter will tell Find not to consider upper or lower case letters. It will carry out case-insensitive searches.

Save the code and go back to the worksheet. Then, select the cell where you want to place your resultant value and type the following formula in the selected cell or into the Formula Bar.

=FindStringInColumn(D4:D14,G4)

Here, in the function FindStringInColumn taken the column range D4:D14 to find the string value of G4 which indicates Computer.

Finally, press the ENTER key. Therefore, you will see the location of the given string value which is D6.

Find String Value in Column using VBA


Similar Readings:


4. Find Non Empty Cell in Column

You also can write VBA functions to find non-empty cells in a column.
Here, I’m going to demonstrate how you can write a function to find the last non-empty cell in a column in Excel.

To begin with, open the Developer tab >> then select Visual Basic

Find Non Empty Cell in Column using VBA

It will open a new window of Microsoft Visual Basic for Applications.
Now, from Insert >> select Module

A Module will be opened.
Then, write the following code to create the function.

Function FindLastNonEmptyCellColumn(MyColumn As String) As String
    With Application.Caller.Parent
        FindLastNonEmptyCellColumn = .Range(MyColumn & .Rows.Count).End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
End Function

Find Non Empty Cell in Column using VBA

Here, I named the function as FindLastNonEmptyCellColumn

This function will accept a single argument which is MyColumn
MyColumn is the column you search in.

Here, used With statement.

In Range provided the parameters MyColumn and Row.Count. Rows.Count will count the row number from the last used row as here I also used End(xlUp).

After writing the code, Save the code and go back to the worksheet.

Now, select the cell where you want to place your resultant value.
Then, type the following formula in the selected cell or into the Formula Bar.

=FindLastNonEmptyCellColumn(G4)

Here, in the function FindLastNonEmptyCellColumn taken the column header B to find the last non-empty cell of that column.

In the end, press the ENTER key. Hence, you will see the last non-empty cell of column B which will be B15.

Find Non Empty Cell in Column using VBA

5. Find First Empty Cell in Column

You can write VBA functions to find the first empty cell in a column.
To write a function to find the first empty cell in a column in Excel follow the explained procedure.

At first, open the Developer tab >> then select Visual Basic

Find First Empty Cell in Column using VBA

A new window of Microsoft Visual Basic for Applications will pop up.
Now, from Insert >> select Module

A Module will be opened.
Then, write the following code to create the function.

Function FindFirstEmptyCellColumn(MyColumn As String) As String 
    With Application.Caller.Parent.Columns(MyColumn)
        FindFirstEmptyCellColumn = .Find(What:="", After:=.Cells(.Cells.Count), _
        LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
End Function

Find First Empty Cell in Column using VBA

Here, I named the function as FindFirstEmptyCellColumn

This function will accept one argument which is MyColumn.
MyColumn is the column you search in.

Then, used the Range.Find method within the With statement.

In What parameter used “” which will find/search the empty values in.

After parameter will specify after which the search begins.

LookIn:=xlFormulas parameter will look for formulas if there are any.

In the LookAt parameter provided xlWhole to look at the whole cell search contents.

SearchOrder:=xlByRows parameter will move horizontally and also loops up through each row until it finds a non-empty cell.

SearchDirection:=xlNext parameter will search for the next empty match.

After writing the code, Save the code and go back to the worksheet.

Now, select the cell where you want to place your resultant value.
Then, type the following formula in the selected cell or into the Formula Bar.

=FindFirstEmptyCellColumn(G4)

Here, in the function FindFirstEmptyCellColumn taken the column B to find the first empty cell.

Finally, press the ENTER key. Then, you will see the location of the first empty cell which is B2.

Find First Empty Cell in Column using VBA

6. Find Next Empty Cell in Column

Like the last two, you also can define a function to find the next empty cell in a column. Here, I’ll explain the procedure to write a function to find the next empty cell in a column in Excel.

Here, I have modified the dataset (brought an empty cell) to demonstrate to you with an example.

First, open the window of Microsoft Visual Basic for Applications through the following steps Developer tab >> then select Visual Basic

Find Next Empty Cell in Column using VBA

Then, from Insert >> select Module

A Module will be opened.
Then, write the following code in the Code Window to create the function.

Function FindNextEmptyCellColumn(MySourceCell As Range) As String   
    With MySourceCell(1)         
        If IsEmpty(MySourceCell(1)) Then
            FindNextEmptyCellColumn = .Address(ReferenceStyle:=xlR1C1)       
        ElseIf IsEmpty(.Offset(1, 0)) Then
            FindNextEmptyCellColumn = .Offset(1, 0).Address(ReferenceStyle:=xlR1C1)
        Else
            FindNextEmptyCellColumn = .End(xlDown).Offset(1, 0).Address(ReferenceStyle:=xlR1C1)
        End If    
    End With
End Function

Find Next Empty Cell in Column using VBA

Here, I named the function as FindNextEmptyCellColumn.

This function will accept a single argument which is MyColumn.
MyColumn is the column you search in.

Next, I used If and ElseIf statements to conditionally execute a set of parameters.

End(xlDown) is the Range.End property that returns a range object representing the cell at the end of the region containing the source range.

Offset(1, 0) is the Range.Offset property, it returns a range object representing a cell range at an offset from the source cell range.

This function will return the address (as an R1C1-style absolute reference)
Here, R will represent the row number and C will represent the column number.

After writing the code, Save the code and go back to the worksheet.

Now, select the cell where you want to place your resultant value.
Then, type the following formula in the selected cell or into the Formula Bar.

=FindNextEmptyCellColumn(B9)

Here, in the function FindNextEmptyCellColumn taken the B9 as MySourceCell to find the next empty cell.

Finally, press the ENTER key. As a result, you will see the row and column number of the next empty cell from the selected cell. The next empty cell will be R10C2.

Find Next Empty Cell in Column using VBA

Here, I’ve chosen B12 as MySourceCell to find the next empty cell. The next empty cell of B12 is R15C2.

7. Find and Mark in Column

You can write VBA code to find and mark a column. Let’s get to know the procedure.

To begin with, open the Developer tab >> then select Visual Basic

Find and Mark in Column using VBA

It will open a new window of Microsoft Visual Basic for Applications.
Now, from Insert >> select Module

A Module will be opened.
Then, write the following code to create the function.

Sub Find_Mark_in_Column()
Dim strFirstAddress As String
Dim rngFindValue As Range
Dim rngSearch As Range
Dim rngFind As Range
Set rngFind = ActiveSheet.Range("D1:D14")
Set rngSearch = rngFind.Cells(rngFind.Cells.Count)
Set rngFindValue = rngFind.Find("Laptop", rngSearch, xlValues)
If Not rngFindValue Is Nothing Then
   strFirstAddress = rngFindValue.Address
   rngFindValue.Font.Color = vbRed
   Do
     Set rngFindValue = rngFind.FindNext(rngFindValue)
     rngFindValue.Font.Color = vbRed
  Loop Until rngFindValue.Address = strFirstAddress
End If
End Sub

Find and Mark in Column using VBA

Here, I’ve created a sub-procedure called Find_Mark_in_Column. Where declared strFirstAddress as String and rngFindValue, rngSearch, rngFind as Range.

Provided the active sheet range D1:D14 also the find value is Laptop.
Then, used an IF statement to check whether the value is found or not.
If the value is found, then it will mark vbRed on the value.

Now, Save the code and go back to the worksheet.
Here, open the View tab >> from Macros >> select View Macros

Then, a dialog box will pop up.

Find and Mark in Column using VBA

Now,  from the Macro name select the Find_Mark_in_Column also select the workbook within Macros in.

Finally, Run the selected Macro.
Thus, it will mark red color on the found values.

Find and Mark in Column using VBA

Practice Section

I’ve provided an extra sheet to practice all these explained methods by yourself.

Practice Sheet

Conclusion

In this article, I’ve explained 7 methods to VBA find in a column in Excel. You can follow any of the methods to find the value in the column. In case you have any confusion or question regarding these methods you may comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo