vba find the last cell used in a column in Excel

Q151. In Excel, how can I find the last used cell in a Column in VBA?

For example, in the table below in "Sheet1" of my WorkBook, I would like to find the last cell in the First Name column.

excel vba find the last cell used in a column

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub LastRowLastColumn()
  3. Dim lngLastRow As Long, lngLastColumn As Long
  4. ''
  5. lngLastRow = ThisWorkBook.Sheets("Sheet1").Cells(ThisWorkBook.Sheets("Sheet1").Rows.Count, 1).End(xlUp).Row
  6. lngLastColumn = ThisWorkBook.Sheets("Sheet1").Cells(2, ThisWorkBook.Sheets("Sheet1").Columns.Count).End(xlToLeft).Column
  7. ''
  8. End Sub


a) In row 5, item marked in red represent the column no. (in this case First Name is in Col A, hence the value is one) where last used cell is to be determined.

b) Row 6 is used to determine last used cell in a row.

c) In row 6, item marked in red represent the row no. where last used cell is to be determined.


You can find similar Excel Questions and Answer hereunder

1) How can worksheet functions be accessed in VBA?

2) I am using Excel for Project Management - how can I find End Date for a task given Start Date and Task duration considering only working days?

3) How can I loop through all WorkSheets and get their names using VBA?

4) How can I copy and rename a WorkSheet using VBA?

5) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?

6) How can I add a legend to a chart using VBA?

7) How can I activate a routine when there is a change in value of a cell?

8) How can I sort data using VBA?

9) How can I identify all cells with Data Validation in my WorkSheet?

10) I want to add a trend indicator symbol next to my sales data - how can I do that?

11) How can I filter and copy only filtered data using VBA?

12) How can I protect / unprotect WorkSheet using VBA?

13) How can I get users to select a file for processing using my macro?

14) How do I enter a formula in a cell using VBA (using Absolute Reference)?

15) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?