Vba last used column in Excel

In the previous chapter, we discussed about last used row.

This chapter deals with last used column in a worksheet. This also has its own importance in programming.

Similar to last used row, there might arise some occassions, where the new data has to be appended along a single row in multiple columns.

For this, the script should know about the last used column in a sheet.

This is where, this logic is used.

Unlike previous chapter, The last used column is found from Cells() object.

The Syntax is

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

The same logic as before is used here.The excel traverses from very last column

until it finds a non-empty cell in row 1.

The following example illustrates this:

Note that, for demo purpose, we have put a value at Cell N15.

So we are going to find the last used column in Row 15, which is N(14th Column)

  1. Sub last_col()
  2. Dim lcol As Integer
  3. With Sheets("Q34")
  4. lcol = Sheets("Q34").Cells(15, .Columns.Count).End(xlToLeft).Column
  5. End With
  6. MsgBox lcol

The Screenshot of the editor is as shown below:

excel vba last used column


You can find similar Excel Questions and Answer hereunder

1) I track a stock on a daily basis and enter the Open, High, Low and Close values for every trading day. In Excel, how can I automatically get High and Low values for the last 10 trading days?

2) How can I extract First Name and Last Name from a cell that has Full name?

3) How to freeze a row or column in Excel

4) Autofill a b c d aa ab in Excel

5) How can I get the last non-zero value in a row?

6) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

7) How can I find the last used cell in a Column in VBA?

8) How to find the column number from the cell address in VBA

9) How do you know which row was used the last. Here the explanation to find it with VBA

10) How to find the cell address from the column number in VBA


Here the previous and next chapter