+ Reply to Thread
Results 1 to 10 of 10

Count rows including blanks till the last used cell in a given range

  1. #1
    Registered User
    Join Date
    08-26-2020
    Location
    sweden
    MS-Off Ver
    2019
    Posts
    16

    Count rows including blanks till the last used cell in a given range

    Hi!

    Im having trouble getting my function to work the way i want to, as the header says I want to count number of cells in the range I3:I800 but only to the last used cell.
    Example:
    I3:data
    I4:data
    I5:data
    I6:empty
    I7:data
    I8:empty
    I9:empty
    I10:data
    I11:empty
    I12:empty
    I13:empty
    ...

    Last used cell in the range is I10, I want a function that can count those 10 with content and the blanks but skip the blanks after the last used cell (I10).
    Is it possible? Thanks in advance!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count rows including blanks till the last used cell in a given range

    Hi,

    As long as the non-blank entries in that range are non-numeric and that the 'blanks' after the last used cell are empty (and so do not contain the null string (""), e.g. as a result of formulas in those cells):

    =MATCH("Ω",I3:I800)


    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    08-26-2020
    Location
    sweden
    MS-Off Ver
    2019
    Posts
    16

    Re: Count rows including blanks till the last used cell in a given range

    You're the man!!! Thanks alot <3

  4. #4
    Registered User
    Join Date
    08-26-2020
    Location
    sweden
    MS-Off Ver
    2019
    Posts
    16

    Re: Count rows including blanks till the last used cell in a given range

    A follow-up question, im trying to put it in a code that makes cell B8 show the number counted;

    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=PASSA("?";I3:I800)"

    as you can see the omega isn't turns into a ?, is it possible to solve else way?


    EDIT; PASSA is the Swedish version.

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count rows including blanks till the last used cell in a given range

    Use "zzz" instead (assuming there are no entries in your range which come after that string alphabetically).

    Regards

  6. #6
    Registered User
    Join Date
    08-26-2020
    Location
    sweden
    MS-Off Ver
    2019
    Posts
    16

    Re: Count rows including blanks till the last used cell in a given range

    Thanks again!

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count rows including blanks till the last used cell in a given range

    You're welcome!

    Cheers

  8. #8
    Registered User
    Join Date
    08-26-2020
    Location
    sweden
    MS-Off Ver
    2019
    Posts
    16

    Re: Count rows including blanks till the last used cell in a given range

    Sorry to bother even more but I can't understand the way to input the range in VBA code.
    Coding: ActiveCell.FormulaR1C1 = "=MATCH(""zzz"";I3:I800)" gives error because I can't use I3:I800.
    So i tried recording a macro and checking the outcome code and it generated something wierd like;
    ActiveCell.FormulaR1C1 = "=MATCH(""zzz"",R[1]C[-19]:R[798]C[-19])"
    Which is okay if it did work but the real data written to that cell turns out to be: =MATCH("zzz";GX9:GX806) Which is totally wrong cell range? how come..

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count rows including blanks till the last used cell in a given range

    No bother at all.

    Use ActiveCell.Formula not ActiveCell.FormulaR1C1.

    Regards

  10. #10
    Registered User
    Join Date
    08-26-2020
    Location
    sweden
    MS-Off Ver
    2019
    Posts
    16

    Re: Count rows including blanks till the last used cell in a given range

    You're the best really!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to count blanks and non-blanks with a dynamic range
    By brittdyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2018, 07:45 AM
  2. Alpha Sort Rows including blanks at the bottom
    By VisionSmart in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-22-2017, 03:47 AM
  3. [SOLVED] LOOKUP A VALUE IN A ROW and COUNT Number of BLANKS Below it till NEXT Value
    By learnall18 in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 05-18-2016, 08:42 AM
  4. Dynamic Range Sizes, including blanks
    By Speshul in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2014, 10:52 AM
  5. Search for specific data and count number of rows till empty Cell
    By kjanani30 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2013, 06:05 AM
  6. Replies: 9
    Last Post: 10-12-2010, 12:37 PM
  7. Create dynamic dropdown from range including blanks
    By Mike Mick in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2006, 12:00 PM

Tags for this Thread

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