check if a range is empty in vba module

2
votes

I wanted to check if an excel range in empty in a section of code in user module. I used the below code

Worksheets(yearsheet).Range("N" & rownum & ":DI").Select
If Application.WorksheetFunction.CountA(Selection) = 0 Then
    Exit Sub
End If

I'm getting runtime error 1004. Can anyone tell whats my mistake?

Thanks in advance. PS: rownum is integer variable and yearsheet is string variable. both these variables were updated properly in code prior to the above section of the code

2
excelvba

2 Answers

2
votes

"N" & rownum & ":DI" doesn't evaluate to a real address because it's missing the row number for the second half of the address. Also, you should avoid using Select statement whenever possible.

Assuming the whole range is in one row, this would work:

Sub test()
Dim yearsheet As String
Dim rownum As Integer

yearsheet = "Sheet2"
rownum = 2
If Application.WorksheetFunction.CountA(Worksheets(yearsheet) _
        .Range("N" & rownum & ":DI" & rownum)) = 0 Then
    Exit Sub
End If
End Sub
0
votes

The best way to test if a selection is (not) empty in VBA:

' Tests if a selection of cells exists.
' @return true or false
Function isCellSelection() As Boolean
    Dim r As range
    isCellSelection = False
    Set r = Selection.Cells
    If IsEmpty(r) Then
        isCellSelection = True
    End If
End Function ' isCellSelection