Excel Find Last Column With Data (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample dataset


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.

Using TAKE Function

Note: The TAKE function will not be available in all versions of Microsoft Excel. Here, I’m using Microsoft Office 365 version of Excel.

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.

Using Excel TAKE & ROWS to Find Last Column With Data


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

Using MIN & COLUMN then INDEX Function to Find Last Column With Data

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)

Using MIN & COLUMN then INDEX Function to Find Last Column With Data

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).

Using VBA to Find Last Column With Data

  • 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

VBA Code to find last column & last data

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.

Using VBA to Find Last Column With Data

As a result, it will show the last column value in a message box.

Find last column & last data through VBA


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained ways. You can download it from the above link.

Practice Sheet to Find Last Column with Data


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


<< Go Back to Find Value in Range | Excel Range | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo