Best way to get the last row of any column of the excel sheet

We often need to find the last row or last column having data in a excel sheet, when we create VB applications for excel. This last data may be in any row or any column. Some times we need to get the last row or last column of whole work sheet. And some times we need to get the last row of specific column or last column of specific row. So we need to use suitable code according to our requirement. Here below are different codes you can use for different kinds of situations.

If you need to get the last row whole worksheet you can use below code for that kind of scenario.

'find last row Lastrow = ws1.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Here Lastrow equals to last row number having data. So if you want to enter data to next row you must use Lastrow+1. Last data might be in any column. It gives answer without depending on the column.

Some times you need to get last row number having data in a specific column. For a example if you need to get  last row number having data in column "A" you can use below code.

'column A in this example LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

You need to put above code inside a with block as follows

dim WS as worksheet set WS=worksheets("sheet1") With WS      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End with

If you need to get  last column having data in row 1 you can use below code.

'row 1 in this example LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column