# Simple method for Excel row count



## peterlo (Jul 30, 2007)

Hello,
I am a relative newcomer to Excel & stuggling a little.
My OS is XP Pro SP2 & Excel version is 2000 at home & 2007 at work.
I would like to establish a simple reliable method for a simple row count of any worksheet I am using.
The best I have so far works when the rows are sequentially numbered & where there are no blank cells:- use CNTL + Down Arrow; takes you to bottom of table & you can read the last row no.
There has to be an easy way for something so simple, without these limitations ,can you please help.
Rgds
Peter O


----------



## Jimmy the Hand (Jul 28, 2006)

Welcome to TSG Forums 

If you are looking for a VBA code, you can use this:


```
Function RowCount() As Long
    RowCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub
```
Without code, but doing basically the same, use CRTL + End keys. It takes you to the last used cell, regardless of empty cell in between.

Jimmy


----------



## peterlo (Jul 30, 2007)

Jimmy,

That was pretty quick.
So at last I have a use for a keyboard key (that for 12 years or so) I did not know even existed.
I just found it (the END key!!!!) & your solution is sublimley simple to do & more to the point to remember.
Regarding the code, I had better learn how to use it.
Would you mind just outlining the key steps, do I turn it into a macro?
I have been experimenting for hours recording macros & it sounds easy but I seem to strike one problem after another, for example how do I open the Personla Macro Workbook, I can't find it!
Many thanks the speed of your response is most appreciated.
Pete


----------



## Jimmy the Hand (Jul 28, 2006)

Peter,

The function I showed is not unlike any other functions. It can be used in any equation, e.g.

```
A = RowCount
B = RowCount + 16
etc.
```
or it can be sent to an output channel, e.g. the screen:

```
MsgBox RowCount
```
There are other uses as well. If you have ever done porgramming before, using a function must be familiar to you.

On the other hand, if you are new to programming, then your lack of the basics would make my explanations tedious and inefficient, I guess. A "more to the point" approach would be desirable here as well. Like you tell me what you want the macro to do, and I (or someone else here) tell you how to do it.

Jimmy


----------



## peterlo (Jul 30, 2007)

Jimmy,
Unfortunately as I first said I am a stuggling newcomer to Excel.
BTW finally got that small macro recorded so I am getting better daily.
I wanted a simple row count that would work after deletions from filtering or the presence of blanks.
Quite happy with code, but again as indicated, I might not be able to implement without some messing about/learning etc.
Wonderful to know people such as yourself are willing to help but long term I have to help myself by putting in some effort to learn & understand. 
Recognise that explanations, particularly step by step is tedious but right now I need a bit more than just code.
Hope you understand & please be aware I will be happy if you think your time might be better spent now helping elsewhere.
Pete


----------



## Jimmy the Hand (Jul 28, 2006)

Peter,

Giving step by step explanations on pieces of code is no stranger to me. But this is beside the point. The point is that there are different approaches to solving a problem through VBA code.

One approach is that you start from scratch, with the theory of variables, looping, conditionals, objects, etc., basically, you start learning how to _think_ in a programmer's way. You keep learning, all the while ignoring the original problem, until you reach the level of knowledge where you can solve your original problem on your own. This is a long road, and there are a lot of written guides along. I think this forum is not the place to take programming courses.

However, this forum is a good place to start, when you have an actual problem and need to find a solution to it, regardless of your current programming skill. People here might give you ideas, guidelines or full solutions, as you wish, and you might as well pick up some knowledge along the way. But in order to do this, we need a clear description of the problem itself.

To get a picture about what a "clear description" means, let's see an example.
Here's what you imparted so far:


> I would like to establish a simple reliable method for a simple row count of any worksheet I am using.
> I wanted a simple row count that would work after deletions from filtering or the presence of blanks.


You also said that you wanted to implement it in code.

What I miss is what you want to do with the data. You could say, for instance, that


> "I want a macro that counts the used rows on the active sheet, and...


... shows me this data on the screen so that I can read it."
... shows me this data on the statusbar so that I can read it."
... writes this data into a textfile."
... sends this data via email, to a certain address."
... puts this data in a particular cell so that other operations can be executed depending on the value."
etc., etc.

Do you see? The way you put the problem is way too vague for me to suggest anything useful. You need to be able to describe your conception clearly.

Jimmy


----------



## peterlo (Jul 30, 2007)

Jimmy,
You put a lot of thought into your post & I thank you.
I wish to try & answer with the same consideration:
First you know by now my level of ability.
Second my question was not about a "mission critical" application, I can get by with the use of CTRL+End & CTRL+up/dwn arrow keys.

I had initially a number of Excel questions & I thought it would be wise to ask first what I expected to be the simplest, IE "How to get a row count"?
I know now the question is too open ended.
What I have found working with large files up to about 125,000 rows is that after filtering & sorting & deleting I loose up to 10% & I wished to be in a position to quickly keep check of what the row count was.
I am not interested in including blank rows, I don't know why they remain listed actually. I think these are called inactive cells.
So regardless of whether any row has blank cells within, if it contains data I would like that row to be "in".
Running a macro would be tedious I believe, might just as well do a series of Ctrl/down arrow operations.
I suppose my question now becomes is there a slicker way, can I for instance cause a msg box/status bar or similar to auto report the count if so selected.
If so, is it relatively easy & worth the effort of helpers on this forum?
Hope this clarifies things.
Best Rgds,
Pete


----------

