Find the Last Column with Data in Excel VBA

Add to Favorites

How to find the last column in a row that has data. This includes selecting that column or a cell in it, returning the column number, and getting data from that column.

Sections:

Find the Last Column with Data

Select the Last Cell

Select the Entire Column

Get the Number of the Last Column

Get the Data from the Last Column

Notes

Find the Last Column with Data

Cells(1, Columns.Count).End(xlToLeft)

431e69acb27d2882f7c9d3e591668613.jpg

All you have to change to make this work for you is the 1.

This is the basic method that you use to return the last cell in a row that has data. That means that the cell will be in the last column that has data.

The 1 in the above code is the number of the row that you want to use when searching for the last column that has data. Currently, this code looks in row 1 and finds the last column in row 1 that has data. Change this to any number you want.

This doesn't seem very useful right now, but, in practice, it is often used within a loop that goes through a list of rows and you can then set this code to find the last column of data for each row without having to manually change this number.

Columns.Count puts the total number of columns in for that argument of the Cells() function. This tells Excel to go to the very last cell in the row, all the way to the right.

Then, End(xlToLeft) tells Excel to go to the left from that last cell until it hits a cell with data in it.

This basic format is also used to find the last row in a column.

Now, let's look at how to get useful information from this code.

Select the Last Cell

The first thing that you might want to do is to select the cell that has data in the last column of a row.

Cells(1, Columns.Count).End(xlToLeft).Select

fda93088fec927d3a8259fa3cfb6cf54.jpg

Select is what selects that last cell in the row that has data.

This is exactly the same as the first piece of code except we added Select to the end of it.

1 is the row that is being checked.

Select the Entire Column

This selects the entire column where the last cell with data is found within a particular row.

Cells(1, Columns.Count).End(xlToLeft).EntireColumn.Select

7d8343740427b93dc5a2a8bcc1ee9b0c.jpg

EntireColumn is added to the base code and that is what references, well, the entire column.

Select is added to the end of EntireColumn and that is what does the actual "selecting" of the column.

1 is the row that is being checked.

Get the Number of the Last Column

Let's get the number of the last column with data. This makes it easier to do things with that column, such as move one to the right of it to find the next empty cell in a row.

Cells(1, Columns.Count).End(xlToLeft).Column

9cf4b0781eb60003d98f7ba87283794b.jpg

Column is appended to the original code in order to give us the number of the column that contains the last piece of data in a row.

This is rather useless on its own though, so let's put it into a variable so it can be used throughout the macro.

last_col = Cells(1, Columns.Count).End(xlToLeft).Column

8eb55f6df072cb0a80779790bccc8816.jpg

The number of the last column is now stored in the variable last_col and you can now use that variable to reference this number.

1 is the row that is being checked.

Get the Data from the Last Column

Return any data contained in the cell in the last column.

Cells(1, Columns.Count).End(xlToLeft).Value

16fcf6c6b0f5e5f7874e29b4e626c0de.jpg

Value is added to the end of the base code in order to get the contents of the cell.

This is rather useless in its current form so let's put it into a variable.

cell_value = Cells(1, Columns.Count).End(xlToLeft).Value

6844d187a3066452ac2102510c5eaf93.jpg

Now, the variable cell_value will contain anything that is in the last cell in the row.

1 is the row that is being checked.

Notes

The basic format for finding the last cell that has data in a row is the same and is the most important part to remember. Each piece of information that we want to get from that last cell simply requires one or two things to be added to the end of that base code.

Download the attached file to work with these examples in Excel.


Downloadable Files: Excel File

Similar Content on TeachExcel
Get the Last Row using VBA in Excel
Tutorial: How to find the last row of data using a Macro/VBA in Excel, including getting the number ...
How to Add a New Line to a Message Box (MsgBox) in Excel VBA Macros
Tutorial: I'll show you how to create a message box popup window in Excel that contains text on mult...
Loop Through an Array in Excel VBA Macros
Tutorial: I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly ...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Sort Data Left to Right in Excel
Tutorial: How to sort columns of data in Excel.  This is the same as sorting left to right. This wil...
Format Cells in The Short Date Number Format in Excel
Macro: This free Excel macro applies the Short Date number format to a selection of cells in ...