CODEWITHSUNDEEP
vbaexcel
Shiva prasad
Shiva prasad

Reputation: 27

Find number of rows in a column of a particular sheet in macros

I am struggling to write a function in VBA which takes sheet name and column as parameters and returns last used row number in that column of that sheet.

I used the following code:

Worksheet("sheetname").Range("e2").End(xlDown).Row

But it returns wrong values... like 104567

Please provide me the code to find row count in a particular column of particular sheet.

Upvotes: 1

Views: 1222

Answers (2)

user8221156
user8221156

Reputation:

An explanation: The number is actually 1,048,576 - the bottom of the worksheet. What it means is that there are no non-empty cells in the column "E:E" below row 2. The solution provided by braX is a standard and a very good way of handling this. In case you want to spec the column by name, you can do this:

Worksheet("sheetname").Range("e" & Worksheet("sheetname").Rows.Count).End(xlUp).Row

Upvotes: 1

braX
braX

Reputation: 11755

The 1 in the code is the column you would like to check. You can change ActiveSheet to any other valid worksheet object.

lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

Upvotes: 6

Related Questions