Consulting

Results 1 to 3 of 3

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  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

Posting Permissions

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