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.

=TAKE(B3:D9,-1,-1)

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.

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.

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.

=TAKE(B3:D9,-(ROWS(B3:D9)-1),-1)

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.

Using Excel TAKE & ROWS to Find Last Column With Data

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.

=MIN(COLUMN(A4:C9))+COLUMNS(A4:C9)-1

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

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.

=INDEX(A4:C9,6,F3)

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

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

Using VBA to Find Last Column With Data

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

VBA Code to find last column & last data

Code Breakdown

We declared the Sub procedure Find_Last_Column_with_Data.
You should provide any valued row number in the Cells object.
We used the COUNT method to count the last column and used the Select property to select the entire last column.
We stored the total row number of the range in my_row variable. Supply any valued column number.
We used the VALUE method to get the last value of the last column. Here, I stored the value in the cell_value variable.
To show the value, we used MsgBox.

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.

Using VBA to Find Last Column With Data