Count empty cells within used Rows

Question

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

`Range("B1":"B" & LastRow)` should be `Range("B1", "B" & LastRow)` or `Range("B1:B" & LastRow)`. And I recommend to specify for each `Range` in which worksheet that range is. Like `Worksheets("TASK").Range…` — Pᴇʜ, May 19 '21 at 12:23
Do not use `UsedRange` else you may get incorrect blank count. Find the last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then find the blank cells in the relevant range using `WorksheetFunction.COUNTBLANK()` as @АлексейР suggested. — Siddharth Rout, May 19 '21 at 12:29
@SiddharthRout If needs to count the gaps in the 'table' column (range), then targeting the last row in a particular column can reduce the number of gaps - if there are gaps at the end of the column in the table. — Алексей Р, May 19 '21 at 12:40

score 1 · Accepted Answer · answered May 19 '21 at 12:53

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

Count empty cells within used Rows

1 Answers1