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

[Yeliz T]

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.

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 tags courtesy of www.thecodenet.com

[Trebor76]

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:

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

VBA tags courtesy of www.thecodenet.com

HTH

Robert

[Yeliz T]

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