# 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

Get the Number of the Last Column

Get the Data from the Last Column

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

## Question? Ask it in our Excel Forum

Tutorial: How to find the last row of data using a Macro/VBA in Excel, including getting the number ...

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 loop through an array in VBA and macros in Excel. This is a fairly ...

Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...

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

Macro: This free Excel macro applies the Short Date number format to a selection of cells in Exce...