Excel Find Last Column With Data: 4 Quick Ways

Method 1 – Use the TAKE Function to Find the Last Data of the Last Column

• Select any cell to place your resultant value.
• We selected the cell F4.
• Type the following formula in the Formula Bar or into the selected cell.

In the TAKE function, I want to get the Last Column Data for the range B3:D9. –1 denotes the last row of this array, and –1 denotes the last column.

• Press the ENTER key. You will see the last column data of the dataset.

Method 2 – Using TAKE & ROWS Function to Separate All Data of the Last Column

• Select any cell to place your resultant value.
• We selected the cell F4.
• Type the following formula in the Formula Bar or into the selected cell.

In the TAKE function, I’ve selected the entire range B3:D9 as an array. The ROWS function will return the total number of rows of the array. -1 will subtract the row for headers from the counted rows. -1 denotes the last column.

• Press the ENTER key, and you will see the last column of data of the dataset.

Method 3 – Using MIN & COLUMN, and 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.

• We selected the cell F3.
• Type the following formula in the Formula Bar or into the selected cell.

In the COLUMN function, select the range A4:C9 as a reference; it will return an array that contains all column numbers for the given range.
To get the first column number, use the MIN function.
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.

• Press the ENTER key. Then, you will get the last column number.

Let’s continue the procedure to get the last column data.

• Select any cell to place your resultant value >> We selected cell F4.
• Type the following formula in the Formula Bar or the selected cell.

In the INDEX function, select the range A4:C9 as an array, select 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).
Using the 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 of data.

Method 4 – Using VBA to Find the Last Column with Data

• Open the Developer tab >> select Visual Basic (Keyboard Shortcut ALT + F11).

• Open a new window of Microsoft Visual Basic for Applications.
• There, open Insert >> select Module.

• A Module will open, and 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

• Save the code and go back to the worksheet.
• Open the View tab >> from Macros >> select View Macros.

• A dialog box will pop up.
• From the Macro name select the Find_Last_Column_with_Data. Also, select the workbook within Macros in.
• Run the selected Macro.

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

Download Workbook to Practice

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