How to Find in Column Using VBA in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, there are different methods to find a value or particular item in the column by using VBA.  In this article, we are going to discuss seven different methods to find in a column using VBA in Excel. Now, let’s start this article and explore these methods.
The following image demonstrates the overview of the methods that we have used in this article.

Overview of the methods to find values in column using VBA in Excel


Download Practice Workbook


7 Effective Methods to Find in Column Using VBA in Excel

In this section of the article, we will discuss seven easy methods to find values in a column using VBA 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


1. Finding Specific Value

You can write VBA functions to find values in a column. In this section, we are going to write a function to find the value in a column in Excel. Now, let’s follow the steps mentioned below.

Steps:

  • Firstly, open the Developer tab from Ribbon.
  • Then select the Visual Basic option.

Using the Developer option to open VBA editor

It will open a new window of Microsoft Visual Basic for Applications.

  • Now,  go to the Insert tab.
  • After that, select the Module option.
Function finding_value(column As Range, value As Variant) As String
With column
finding_value = .Find(What:=value, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End With
End Function

Inserting new Module in method 1

As result, a new Module will be opened.

  • Then, write the following code in the Module to create the function.
Writing VBA code

Code Breakdown

  • Firstly, we named the function finding_value. This function will accept two arguments; these are column and value.
  • Here, the column is the column you search in and the value is the numeric value 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 and the search will start after that.
  • LookIn:=xlValues parameter will define the type of data to needs to be searched and it also can acquire any of the built-in constants or values from the XlFindLookIn listings.
  • 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.
=finding_value(C5:C15,G5)

Here, in the created function finding_value we took the column range C5:C15 to find the value G5 (Malta).

Using a newly defined formula in cell G8

  • Finally, press the ENTER key.

Now, you will see the location of the given value which is C14.

Output obtained after finding a specific value using VBA in Excel


2. Locating Value in Table

You can write VBA code to find values in the column from a formatted table in Excel. In this section, We will define a function to find value from a table column. Now, let’s use the steps mentioned below to create this function to find values from a table column in Excel.

Steps:

  • First things first, open the Developer tab from Ribbon.
  • Then, select the Visual Basic option.

Utilizing the Developer option to open the VBA editor

It will open a new window of Microsoft Visual Basic for Applications.

  • Following that, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Then, select the Module option.

Inserting new Module in method 2

  • Subsequently, the Module will be opened, and then write the following code to create the function.
Function finding_value_in_table(worksheet_name As String, column_index As Long, _
value As Variant, Optional table_index As Long = 1) As String
With ThisWorkbook.Worksheets(worksheet_name).ListObjects(table_index).ListColumns _
(column_index).DataBodyRange
finding_value_in_table = .Find(What:=value, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End With
End Function

Writing VBA code to find a value from a table

Code Breakdown

  • Firstly, we named the function as finding_value_in_table. This function will accept up to four arguments; these are worksheet_name, column_index, value, and table_index (optional).
  • Then, we declared the following variables.
    • worksheet_name is the sheet from which you will search in.
    • column_index is the column number from which you will search the value.
    • value is the numeric value you search for.
    • table_index 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 VBA 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.
=finding_value_in_table("Table",1,G5)

Here, in the created function finding_value_in_table, the worksheet_name is Table. Next, we chose the first column to find the value, Adam Smith.

Using newly defined formula in cell G8

  • Finally, press the ENTER key.

Thus, you will find the location of the selected value which is B5.

Output obtained after using the new formula to find a value from a table using VBA


3. Searching String Value

You can define your own functions to find strings in a column using VBA. Let’s write one. Let’s use the instruction outlined below to create a function to find a string value in a column in Excel.

Steps:

  • Firstly, open the Developer tab from Ribbon.
  • Then, select the Visual Basic option.

Using the Developer option to open the VBA editor

It will open a new window of Microsoft Visual Basic for Applications.

  • After that, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Now, select the Module option.

Inserting a new Module in method 3

As a result, a blank Module will be opened.

  • Then, write the following code to create the function.
Function finding_string_in_column(column As Range, my_string As Variant) As String
With column
finding_string_in_column = .Find(What:=my_string, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End With
End Function

Writing VBA code inside the Module

Code Breakdown

  • Firstly, we named the function finding_string_in_column. This function will accept two arguments; these are column and my_string.
  • The column variable indicates the column you search in and the my_string variable refers to the string or text you search for.
  • Then, used the Range.Find method within the With statement. In What parameter used value which will find/search the value in.
  • After parameter will specify and the search will start after that.
  • LookIn:=xlValues parameter will define the type of data to needs to be searched and it also can acquire any of the built-in constants or values from the XlFindLookIn listings.
  • 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.
  • Following that, 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.
=finding_string_in_column(D5:D15,G5)

Here, in the function finding_string_in_column, we took the column range D5:D15 to find the string value of G5 which indicates Computer.

Using newly created formula in cell G8

  • Finally, press the ENTER key.

Consequently, you will see the location of the given string value which is D7.

Location of the searched string found as displayed in cell G8


Similar Readings:


4. Finding Non Empty Cell

You also can write VBA functions to find non-empty cells in a column. Here, we are going to demonstrate how you can write a function to find the last non-empty cell in a column in Excel. Now, let’s use the procedure discussed in the following section.

Steps:

  • To begin with, open the Developer tab from Ribbon.
  • Then, select the Visual Basic option from the Code group.

Utilizing the Developer option to open the VBA editor

As a result, a new window will open named Microsoft Visual Basic for Applications.

  • Subsequently, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Following that, select the Module option from the drop-down.

Inserting a new Module in method 4

As a result, a new Module will be opened.

  • Then, write the following code to create the function.
Function last_NonEmpty_cell(column As String) As String
With Application.Caller.Parent
last_NonEmpty_cell = .Range(column & .Rows.Count).End(xlUp).Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
End With
End Function

Writing VBA code

Code Breakdown

  • Firstly, we named the function as last_NonEmpty_cell. This function will accept a single argument which is column.
  • Here, column is the column you search in.
  • After that, we used the With statement.
  • In Range provided the parameters column and Row.Count.
  • Here, he 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.
=last_NonEmpty_cell(G5)

Here, in the function last_NonEmpty_cell, we took column header B to find the last non-empty cell of that column.

Using the newly defined formula to find the last non empty cell

  • In the end, press the ENTER key.

Hence, you will see the last non-empty cell of column B which will be B16.

Output got by using the newly created custom formula in cell G8


5. Detecting First Empty Cell

Now, we will learn to find the first empty cell in a column using VBA in Excel. To do this, follow the explained procedure given below.

Steps:

  • Firstly, open the Developer tab from Ribbon.
  • Then, choose the Visual Basic option from the Code group.

Using the Developer option to use the VBA editor

After that, a new window of Microsoft Visual Basic for Applications will pop up.

  • Then, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • After that, select the Module option from the drop-down.

Inserting a new Module in method 5

As a result, a new Module will be opened.

  • Then, write the following code to create the function.
Function first_empty_cell(column As String) As String
With Application.Caller.Parent.Columns(column)
first_empty_cell = .Find(What:="", After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End With
End Function

Writing VBA code

Code Breakdown

  • Here, I named the function as first_empty_cell. This function will accept one argument which is column.
  • Here, the column 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 and the search will start after that.
  • 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.
=first_empty_cell(G5)

Here, in the function first_empty_cell, we took column B to find the first empty cell.

Applying the new user defined function to detect the first empty cell using VBA in Excel

  • Finally, press the ENTER key.

Consequently, you will see the location of the first empty cell which is cell B1.

Location of the frist empty cell of a column is displayed in cell G8


6. Locating Next Empty Cell

Like the last two, you also can define a function to find the next empty cell in a column. Here, we will explain the procedure to write a function to find the next empty cell in a column in Excel. In this method, we have modified the dataset (brought an empty cell) to demonstrate to you an example. Now, let’s follow the steps mentioned below.

Steps:

  • Firstly, go to the Developer tab from Ribbon.
  • After that, choose the Visual Basic option.

Using the Developer option to open the VBA editor

Subsequently, a new window of Microsoft Visual Basic for Applications will open on your worksheet.

  • Following that, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Now, select the Module option from the drop-down.

Inserting a new Module to write VBA code

As a result, a new Module will be opened.

  • After that, write the following code in the Code Window to create the function.
Function next_empty_cell(source_cell As Range) As String
With source_cell(1)
If IsEmpty(source_cell(1)) Then
next_empty_cell = .Address(ReferenceStyle:=xlR1C1)
ElseIf IsEmpty(.Offset(1, 0)) Then
next_empty_cell = .Offset(1, 0).Address(ReferenceStyle:=xlR1C1)
Else
next_empty_cell = .End(xlDown).Offset(1, 0).Address(ReferenceStyle:=xlR1C1)
End If
End With
End Function

Writing VBA code

Code Breakdown

  • Firstly, we named the function as next_empty_cell. This function will accept a single argument which is column.
  • Here, column is the column you search in.
  • Next, we 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.

Note: 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.
=next_empty_cell(B9)

Here, in the function next_empty_cell, we took cell B9 as source_cell to find the next empty cell.

Using newly created formula to find new empty cell using VBA in Excel

  • 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 R11C2.

Row and column number of the next empty cell after cell B9 is shown in cell G5

Here, we have chosen B12 as source_cell to find the next empty cell. The next empty cell of B12 is R16C2.

Row and column number of the next empty cell after cell B12 is shown in cell G5 using VBA in Excel


7. Finding and Marking Values

In this section of the article, we will learn how we can find and mark a column using VBA in Excel. Now, let’s get to know the procedure.

Steps:

  • Firstly, go to the Developer tab from Ribbon.
  • Then, select the Visual Basic option from the Code group.

Using the Developer option to open the VBA editor

Consequently, a new window of Microsoft Visual Basic for Applications will open on your worksheet.

  • After that, go to the Insert tab in the Microsoft Visual Basic for Applications window.
  • Then, choose the Module option.

Inserting a new Module

As a result, a blank Module will be opened.

  • Then, write the following code to create the function.
Sub finding_mark()
Dim str_address As String
Dim range_value As Range
Dim search As Range
Dim range_find As Range
Set range_find = ActiveSheet.Range("D1:D15")
Set search = range_find.Cells(range_find.Cells.Count)
Set range_value = range_find.Find("Laptop", search, xlValues)
If Not range_value Is Nothing Then
str_address = range_value.Address
range_value.Font.Color = vbRed
Do
Set range_value = range_find.FindNext(range_value)
range_value.Font.Color = vbRed
Loop Until range_value.Address = str_address
End If
End Sub

Writing VBA code inside the newly created Module

Code Breakdown

  • Firstly, we’ve created a sub-procedure called finding_mark. Where declared str_address as String and range_value , search, and range_find as Range.
  • Provided the active sheet range D1:D15 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.
  • After that, go to the View tab from Ribbon.
  • Then, choose the Macros option and select View Macros from the drop-down.

Opening the list of created Macros

Subsequently, a dialog box will pop up.

Using the created macro to find and mark specific value using VBA in Excel

  • Now,  from the Macro name select thefinding_mark.
  • Finally, Run the selected Macro.

Consequently, it will mark red color on the found values as demonstrated in the following image.

Specific values are found and marked in red color


Practice Section

In the Excel Workbook, we have provided a Practice Section. Please practice it yourself.

Sample Practice Section provided in the Practice Workbook


Conclusion

In this article, I’ve explained seven methods to find in a column using VBA 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. Also, follow our website, ExcelDemy, a one-stop Excel solution provider to explore more.


Further Readings

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo