Vba find the last cell used in a column in Excel

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

Description:

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 I find the number of working days between 2 dates using VBA?

2) Vba list all files in a folder in Excel

3) How can I set FreezePanes in a certain range using VBA?

4) How can worksheet functions be accessed in VBA?

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

6) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

7) Vba code to password protect workbook in Excel

8) Can I add a small chart to Table data to make it visually appealing and easy to interpret?

9) How do I add a shape using VBA?

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

 

Here the previous and next chapter