Find the Last Column with Data in Excel VBA

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)

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

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

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

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

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

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

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.