In a large worksheet, finding the last column with data manually is time-consuming and not friendly. There are some functions by using which you can quickly find out the last column with data. In this article, I’m going to explain how you can use the functions and VBA to find last column with data in Excel.
To make the explanation lively, I’m going to use a sample dataset that represents order information. The dataset has 4 columns these are Order Date, Order ID, and Amount.
How to Find Last Column with Data: 4 Quick Ways
1. Using TAKE Function to Find Last Data of Last Column
To find the last column with data in Excel you can use the TAKE function where you can look up the last value of the end column of the entire dataset.
Let’s, start the procedure,
- First, select any cell to place your resultant value.
- I selected the cell F4.
- Then, type the following formula in the Formula Bar or into the selected cell.
=TAKE(B3:D9,-1,-1)
Here in the TAKE function, I want to get the Last Column Data for the range B3:D9. The -1 denotes the last row of this array. Similarly, -1 denotes the last column of this array.
- Now, press the ENTER key. Hence, you will see the last column data of the dataset.
2. Using TAKE & ROWS Function to Separate All Data of Last Column
By using the TAKE function and the ROWS function together you can find the last column with data.
Let’s jump to the procedure,
- To begin with, select any cell to place your resultant value.
- I selected the cell F4.
- Then, type the following formula in the Formula Bar or into the selected cell.
=TAKE(B3:D9,-(ROWS(B3:D9)-1),-1)
Here in the TAKE function, I’ve selected the entire range B3:D9 as an array. Next, the ROWS function will return the total number of rows of the array. -1 will subtract the row for headers from the counted rows. Lastly, -1 denotes the last column.
- Now, press the ENTER key, and you will see the last column data of the dataset.
3. Using MIN & COLUMN then INDEX Function
You can also use the MIN function with the COLUMN and COLUMNS functions to get the last column number.
Depending on the last column number you can find the last column data using the INDEX function.
- First, I selected the cell F3.
- Then, type the following formula in the Formula Bar or into the selected cell.
=MIN(COLUMN(A4:C9))+COLUMNS(A4:C9)-1
Here, in the COLUMN function selected the range A4:C9 as a reference, it will return an array that contains all column numbers for the given range.
Now to get the first column number used the MIN function.
Then the COLUMNS function will give the total column number of the selected range A4:C9. Now you can add the total column number with the first column and then subtract 1 to ensure the number of the last column of the range.
- In the end, press the ENTER key. Then, you will get the last column number.
Now, let’s continue the procedure to get the last column data.
- Select any cell to place your resultant value >> I selected the cell F4.
- Then, type the following formula in the Formula Bar or in the selected cell.
=INDEX(A4:C9,6,F3)
Here, in the INDEX function, selected the range A4:C9 as an array then selected the last non-empty row which is 6 as row_number (need to remember row number should be according to the dataset, and you can find the value of any row providing the number).
Next, used F3 cell value as column_number, the formula will return the value of the corresponding row and column number which will be the last column data.
- Press ENTER, and you will get the last column data.
Read More: How to Find Last Cell with Value in Column in Excel
4. Using VBA to Find Last Column with Data
In case you want to show the last column with data you also can use the VBA.
Let’s begin the procedure,
- First, open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11).
- Next, it will open a new window of Microsoft Visual Basic for Applications.
- From there, open Insert >> select Module.
- A Module will open then type the following code in the opened Module.
Sub Find_Last_Column_with_Data()
'This will select the last column
Cells(4, Columns.Count).End(xlToLeft).EntireColumn.Select
my_row = Cells(Rows.Count, 2).End(xlUp).Row
'This will find the last value of last column
cell_value = Cells(my_row, Columns.Count).End(xlToLeft).Value
MsgBox "Last Column With Data is " & cell_value
End Sub
Code Breakdown
- Here, I declared the Sub procedure Find_Last_Column_with_Data.
- You should provide any valued row number in the Cells object.
- Then, I used the COUNT method to count the last column and then I used the Select property to select the entire last column.
- After that, I stored the total row number of the range in my_row variable. Here, you need to supply any valued column number.
- Furthermore, I used VALUE method to get the last value of last column. Here, I stored the value in the cell_value variable.
- To show the value I used MsgBox.
- Finally, Save the code and go back to the worksheet.
- Next, open the View tab >> from Macros >> select View Macros.
- A dialog box will pop up.
- Now, from the Macro name select the Find_Last_Column_with_Data also select the workbook within Macros in.
- Then, Run the selected Macro.
As a result, it will show the last column value in a message box.
Practice Section
I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.
Download Workbook to Practice
Conclusion
In this article, I’ve explained 4 quick ways of finding the last column with data in Excel. Hope these different ways will help you to find the last column with data. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Further Readings
- How to Find Last Row with a Specific Value in Excel
- How to Find Last Non Blank Cell in Row in Excel
- How to Find Highest Value in Excel Column
- How to Find Lowest Value in an Excel Column
- How to Use Excel Formula to Find Last Row Number with Data
- How to Find Last Cell with Value in a Row in Excel
<< Go Back to Find Value in Range | Excel Range | Learn Excel