Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Mentor
    Joined
    May 2010
    Location
    London
    Posts
    307
    Location

    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
    Sub Learning VBA()

    Do
    Practice Most Useful VBA Examples
    Loop Until Become an Expert in VBA

    End Sub

  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
    VBAX Mentor
    Joined
    May 2010
    Location
    London
    Posts
    307
    Location
    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
    Sub Learning VBA()

    Do
    Practice Most Useful VBA Examples
    Loop Until Become an Expert in VBA

    End Sub

Posting Permissions

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