How many rows excel vba

We want to use VBA to find:. Therefore we need an alternative VBA approach to find the entire range of our income statement. The next step is to find the number of used rows with data in this income statement. Assuming that the cells in Column are always populated for used rows, we can use the COUNTA worksheet function to count the number of used cells in column A of the UsedRange of the worksheet.

It is very important to note how it has been called. After running the macro, a message box returns both answers for total number of rows and the number of used rows. The message box shown below is from the Mac version of Excel. However, in the event we have to check through all columns to ensure the entire row is empty, we can use a For-Next loop together with the COUNTA worksheet function.

This will return the number of non-empty cells in Column "A" which is what I think you're after. Hope this helps. You can assign the return value to a variable like this:. If you use a function that includes a column such as column A as shown in other examples, that will only get you the count of rows in that column, which may or may not be what you're going for.

One caveat: if you have formatted rows below your last row with a value then it will return that row number. If there is a slight chance that the last row of the worksheet is not empty, you should add an IsEmpty check to simoco 's solution.

Therefore; following is a function that returns the last used row and check if the last row of the worksheet is empty:. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?

Collectives on Stack Overflow. Learn more. Asked 7 years, 8 months ago. Active 7 months ago. Viewed 42k times. Since there are some blank cells I thought to go from bottom to up. I use the following Range "A".

Select Selection. End xlUp. Select After this execution the active cell is at A which means the row count is My question is how to get this number from the active cell? Improve this question.

Asked 9 months ago. Active 9 months ago. Viewed times. Consider this list in excel: That aims to count those visible rows. Count If ws. Cells x, 1. Improve this question. Alexander Paudak Alexander Paudak 6 6 bronze badges. Does this answer your question? Count returns incorrect number — sous SpecialCells xlCellTypeVisible would be the right approach here Add a comment. Active Oldest Votes. Range "A4". Count If Not r. Cells x, 1 End If Next Debug. Print content An alternative is to use the SpecialCells -method with the parameter xlCellTypeVisible - that will return only the visible cells of a Range.

Range "A10". Columns 1.