Shamima Sultana
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.
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.
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.
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
As result, a new Module will be opened.
- Then, write the following code in the Module to create the function.
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).
- Finally, press the ENTER key.
Now, you will see the location of the given value which is C14.
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.
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.
- 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
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.
- Finally, press the ENTER key.
Thus, you will find the location of the selected value which is B5.
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.
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.
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
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.
- Finally, press the ENTER key.
Consequently, you will see the location of the given string value which is D7.
Similar Readings:
- How to Find String in a Cell Using VBA in Excel (2 Methods)
- Find Substring Using VBA (9 Ways)
- How to Find String with VBA in Excel (8 Examples)
- Find within a Range with VBA in Excel: Including Exact and Partial Matches
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.
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.
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
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.
- In the end, press the ENTER key.
Hence, you will see the last non-empty cell of column B which will be B16.
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.
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.
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
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.
- Finally, press the ENTER key.
Consequently, you will see the location of the first empty cell which is cell B1.
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.
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.
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
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.
- 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.
Here, we have chosen B12 as source_cell to find the next empty cell. The next empty cell of B12 is R16C2.
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.
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.
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
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.
Subsequently, a dialog box will pop up.
- 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.
Practice Section
In the Excel Workbook, we have provided a Practice Section. Please practice it yourself.
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.
