+ Reply to Thread
Results 1 to 3 of 3

Calculating number of rows in worksheet upon opening file

  1. #1
    Registered User
    Join Date
    08-13-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Calculating number of rows in worksheet upon opening file

    Hi,

    I have a workbook that is connected to a CSV file, which copy the contents into a worksheet upon opening the file (column headings are also copied in). I want to find a simple way of being able to calculate how many rows are in the imported file using VBA - this value should be automatically inputted into another cell somewhere else in the spreadsheet so that I can use it with the OFFSET function to reference columns when doing SUMs, COUNTs, etc.

    How do I do this? I'm currently doing it by assuming a maximum bound on the number of rows, e.g. 1000. I create a separate worksheet with values 1:1000 ranging down in Column A, then in Column B having, e.g. in B1, =IF(ISBLANK(OFFSET('Imported CSV Worksheet'!A$1, A1, 0)), 0, 1), and then doing SUM($B$1:$B$1000) to calculate the number of non-empty rows. There must however be a smarter and neater way of doing this.
    Last edited by maver1ck95; 02-18-2011 at 10:12 AM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Calculating number of rows in worksheet upon opening file

    formula:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-13-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Calculating number of rows in worksheet upon opening file

    cheers... knew it would be something simple

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1