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

[Beatrix]

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?:think:

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

[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 & ")")

HTH

Robert

[Beatrix]

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