Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Simple method for Excel row count


(!)

peterlo's Avatar
peterlo peterlo is offline
Computer Specs
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Jul 2007
Experience: Beginner with Excel 2007
30-Jul-2007, 04:15 AM #1
Simple method for Excel row count
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's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
30-Jul-2007, 05:17 AM #2
Welcome to TSG Forums

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

Code:
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's Avatar
peterlo peterlo is offline
Computer Specs
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Jul 2007
Experience: Beginner with Excel 2007
30-Jul-2007, 05:46 AM #3
Reply re Excel row count
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's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
30-Jul-2007, 06:25 AM #4
Peter,

The function I showed is not unlike any other functions. It can be used in any equation, e.g.
Code:
A = RowCount
B = RowCount + 16
etc.
or it can be sent to an output channel, e.g. the screen:
Code:
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's Avatar
peterlo peterlo is offline
Computer Specs
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Jul 2007
Experience: Beginner with Excel 2007
30-Jul-2007, 08:40 AM #5
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's Avatar
Member with 1,220 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: Level 15 Paladin
31-Jul-2007, 03:59 AM #6
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:
Quote:
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
Quote:
"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's Avatar
peterlo peterlo is offline
Computer Specs
Junior Member with 21 posts.
THREAD STARTER
 
Join Date: Jul 2007
Experience: Beginner with Excel 2007
01-Aug-2007, 04:12 AM #7
Reply to Jimmy
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2