Results 1 to 8 of 8

Thread: How to Count blank Cells in a column till last used row

  1. #1

    Question How to Count blank Cells in a column till last used row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi There,

    I have been busy with VBA today and i'm making an overview of the resultcodes in a workbook, only the amount of rows vary each day.

    Is there a method to count the blank cells in a specific column to the last row instead of getting a number like 64,954?

    I need to display the amount of blank cells, i display the number in a different sheet then the data is in.

    The sheet with the data is called "DATATABLE" i now have Range("B40").Value = "=COUNTIF(DATATABEL!C[42],"""")" but this gives me every single blank cell until the end, i just need the blank cells till the last row.

    Thanks in advance!

    Gr. Bas

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Sorry, I'm not really following what you're after here. Could you attach a copy of a stripped down workbook showing what you have on one sheet, and what you'd expect to see on another?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    You could try this array formula

    =COUNTBLANK(OFFSET($B$2,,,MAX(IF(B:B<>"",ROW(B:B)))))

  4. #4
    I'm not great with VBA
    But one idea struck me
    xldown
    row =
    row = row-1
    then if "" count

    You can probably do the code if not I could write it with some little reserch.

  5. #5
    Quote Originally Posted by Tangjoc View Post
    I'm not great with VBA
    But one idea struck me
    xldown
    row =
    row = row-1
    then if "" count

    You can probably do the code if not I could write it with some little reserch.
    You would need to do xlUP, xlDown will stop at the first blank.

  6. #6
    Hi there guy's,

    Thanks for the responses but i also posted it on another forum and i fixed it already, i used the following code:

    Dim bottomA As Integer
    bottomA = Range("AR" & Rows.Count).End(xlUp).Row
    Sheets("Draaiboek Statussen").Range("B40") = Application.CountBlank(Sheets("DATATABEL").Range("AR1:AR" & Sheets("DATATABEL").Cells(Rows.Count, "AR").End(xlUp).Row))

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Thanks for posting back your solution. Just for info, please have a quick read of this article
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Quote Originally Posted by Ken Puls View Post
    Thanks for posting back your solution. Just for info, please have a quick read of this article
    He Ken,

    Thanks for the reply, i just read the article and i understand what you mean. I only have 2 forums where i ask VBA related questions on and i always try to check both, and also reply to both.

    http://www.excelforum.com/excel-prog...37#post3068237 this is the other site where i posted.

    Maybe someone will be interested in it.

    Gr, Bas

Posting Permissions

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