Excel Hints

Results 1 to 3 of 3

Thread: Solved: VBA code to count blank cells in a dynamic range

  1. #1

    Solved: VBA code to count blank cells in a dynamic range

    Hi Everyone ,

    I need to edit below script to count blank cells in dynamic range in column F only in Year6. F9 should display the result. At the moment Countblank formula calculates that but when data range changes it doesn't give the right value. I think OFFSET function would work in this case however I was wondering if there is a way to get it done by VBA script?

    sample files are attached.

    Your help is appreciated.


    [VBA]Sub CopyDynamicDataRange()

    Dim dataWB As Workbook, dataWS As Worksheet
    Dim baseRng As Range, dataRng As Range

    Set baseRng = ThisWorkbook.Worksheets("Year6").Range("A55")
    Set dataWB = Workbooks.Open("S:\Assessment\CB\CBSYr6.xml")
    With dataWB.Worksheets("Sheet1")
    Set dataRng = .Range(.Range("A1"), .Range("A1").End(xlToRight))
    Set dataRng = .Range(dataRng, dataRng.End(xlDown))
    End With

    Set baseRng = baseRng.Resize(dataRng.Rows.Count, dataRng.Columns.Count)

    baseRng.Value = dataRng.Value

    dataWB.Close False

    End Sub[/VBA]
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    ....have the courage to follow your heart and intuition. They somehow already know what you truly want to become. Everything else is secondary.SJ

    accept failure, it will define your success. (vbax)

    giving up is not an option..

  2. #2
    Hi Yeliz T,

    You can use this, though bear in mind that the second time you run it the result will be one less as the code has put the result in cell F9 hence reducing the number of blank cells by at least one:

    [vba]Worksheets("Year6").Range("F9").Value = Evaluate("COUNTBLANK(Year6!F1:F" & Worksheets("Year6").Cells(Rows.Count, "F").End(xlUp).Row & ")")[/vba]

    HTH

    Robert
    Last edited by Aussiebear; 04-30-2012 at 11:45 PM. Reason: Corrected the tags surrounding the code

  3. #3
    Hi Robert ,

    Sorry for replying late. I used the script you gave me and it's working great. However it didn't reduce number of blank cells even I ran it more than twice.. Anyway this is what I was looking for. Thank you so much for your help. Much appreciated..

    Cheers
    Yeliz
    ....have the courage to follow your heart and intuition. They somehow already know what you truly want to become. Everything else is secondary.SJ

    accept failure, it will define your success. (vbax)

    giving up is not an option..

Posting Permissions

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