Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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

Contact Form


Email *

Message *