VBA check if next 10 rows and 10 columns in all 4 sides of a Excel Table is empty

Question

In VBA Excel, if I have a table. How do I check the cells outside the table in all 4 sides of it, for 10 rows and 10 columns, as empty or not?

Thanks Jeevan

Answer 1

You could use this function:

Option Explicit

Function NonBlankCellsOutside(rng As Range, rowsOutside As Long, colsOutside As Long)
    Dim outside As Range
    Dim rowsBefore As Long
    Dim colsBefore As Long

    rowsBefore = IIf(rng.Row <= rowsOutside, rng.Row - 1, rng.Row - rowsOutside)
    colsBefore = IIf(rng.Column <= colsOutside, rng.Column - 1, rng.Column - colsOutside)
    Set outside = rng.Offset(-rowsBefore, -colsBefore) _
                     .Resize(rng.Rows.Count + rowsBefore + rowsOutside, _
                             rng.Columns.Count + colsBefore + colsOutside)
    NonBlankCellsOutside = WorksheetFunction.CountA(outside) _
                         - WorksheetFunction.CountA(rng)
End Function

Example use with a normal range:

Dim ok As Boolean

ok = NonBlankCellsOutside(Worksheets(1).Range("C20:F50"), 10, 10) = 0
If Not ok Then MsgBox "There are non-blank cells in the neighbourhood"

Another example with a named table:

Dim num As Long

num = NonBlankCellsOutside(ActiveSheet.ListObjects("Table1").Range, 5, 5)
MsgBox "There are " & num & " non-blank cells around the table"