Results 1 to 5 of 5

Thread: Count Rows (XP)

  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count Rows (XP)

    A department has a load of CSV downloads on a weekly basis. I am setting up a little application to strip through the data and do various things, like concantenate fields, and get rid off erroneous data etc. It basically loops through the rows of data in a column, offsets to another column and depending on the the format of the contents loops through again.

    When it completes a column of data I am getting it to offset and return to the top of the next column to do its next piece of work . This offset is obviously dependent on the number of rows in the column. At present I am just getting it to loop through adding 1 to the value x :
    ...
    Range("a1").Select
    While ActiveCell <> ""
    x = x + 1
    ActiveCell.Offset(1, 0).Select
    Wend
    RowCount = x
    ....

    Is there a more elegant way off getting a row count ?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Rows (XP)

    Without VBA: the formula =COUNTA(A:A) will count the number of non-blank entries in column A.

    With VBA: if you're certain that there are no gaps:

    RowCount = Range("A1").End(xlDown).Row

    To find the last non-empty row in column A if there might be gaps:

    RowCount = Range("A65536").End(xlUp).Row

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Rows (XP)

    Thanks Hans

    The latter piece of code will do just fine for this particular application as the data is riddled with blank cells due to inputting errors. Having thought this over during postings, if the data is so poor there is a good chance that later columns may have missing data in the last row , therefore causing the offset to error!!!! Something for me to mull over to trap this problem.... time for a cup of tea
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Rows (XP)

    John Walkenbach has a VBA function that returns the "real last cell" in a worksheet, i.e. the intersection of the last non-empty row and the last non-empty column. If you need this, take a look at Determining the Real Last Cell.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts

    Re: Count Rows (XP)

    Jezza,

    If the data doesn't include 'empty' rows, I frequently use something like

    zLastRow = [a1].CurrentRegion.Rows.Count

    This assumes your data starts in row 1, column a but you can adjust as required.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •