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.

Find the Last Column with Data

Get the Number of the Last Column

Get the Data from the Last Column

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

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.

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.

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.

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.

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.

Similar Content on TeachExcel

Get the Last Row using VBA in Excel

Tutorial: (file used in the video above) How to find the last row of data using a Macro/VBA in Exce...

Tutorial: (file used in the video above) How to find the last row of data using a Macro/VBA in Exce...

Next Empty Row Trick in Excel VBA & Macros

Tutorial: A simple way to find the next completely empty row in Excel using VBA/Macros, even if som...

Tutorial: A simple way to find the next completely empty row in Excel using VBA/Macros, even if som...

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

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

Tutorial: I'll show you how to loop through an array in VBA and macros in Excel. This is a fairly...

Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter

Macro: This Excel macro filters a set of data in Excel and displays only the top X percent of tha...

Macro: This Excel macro filters a set of data in Excel and displays only the top X percent of 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 wi...

Tutorial: How to sort columns of data in Excel. This is the same as sorting left to right. This wi...