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.
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")
Set dataRng = .Range(.Range("A1"), .Range("A1").End(xlToRight))
Set dataRng = .Range(dataRng, dataRng.End(xlDown))
Set baseRng = baseRng.Resize(dataRng.Rows.Count, dataRng.Columns.Count)
baseRng.Value = dataRng.Value
Formatting tags added by mark007