1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Simple method for Excel row count

Discussion in 'Business Applications' started by peterlo, Jul 30, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. peterlo

    peterlo Thread Starter

    Joined:
    Jul 30, 2007
    Messages:
    21
    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
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    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
     
  3. peterlo

    peterlo Thread Starter

    Joined:
    Jul 30, 2007
    Messages:
    21
    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
     
  4. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    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
     
  5. peterlo

    peterlo Thread Starter

    Joined:
    Jul 30, 2007
    Messages:
    21
    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
     
  6. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,220
    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:
    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
    ... 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
     
  7. peterlo

    peterlo Thread Starter

    Joined:
    Jul 30, 2007
    Messages:
    21
    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
     
  8. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/602488