CODEWITHSUNDEEP
excelvbacount
M Muaz
M Muaz

Reputation: 91

Count empty cells within used Rows

I have the following VBA code that i want which is to count the number of used rows and then find the number of empty cells in Column B, it's returning a syntax error

Sub Logic()
 'Count Number of used Rows
LastRow = Worksheets("TASK").UsedRange.Rows.count 
Range("O3") = LastRow


'Count Number of Empty cells within the used Row
Dim EmptyCell As Integer
EmptyCell = Application.WorksheetFunction.CountIf(Range("B1":"B" & LastRow), "")
Range("O4") = EmptyCell 

End Sub

Thanks for helping

Upvotes: 0

Views: 635

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

One case is below. It should be borne in mind that .UsedRange will dynamically change when filling the cells of the sheet and can give an unpredictable result - everything depends on the setting of the task and the configuration of the data

Option Explicit

Sub Logic()
    With Worksheets("TASK")
        'Count Number of Empty cells within the used Rows
        .Range("O4") = WorksheetFunction.CountBlank( _
            Intersect(.UsedRange, .Columns("B")))
    End With
End Sub

enter image description here

Upvotes: 1

Related Questions