检查vba模块中的范围是否为空 [英] check if a range is empty in vba module

问题描述

我想检查用户模块中代码部分的Excel范围是否为空.我用下面的代码

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

我遇到运行时错误1004.有人可以告诉我我的错误吗?

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

先谢谢了.PS:rownum是整数变量，yearsheet是字符串变量.在代码的上述部分之前，这两个变量均已在代码中正确更新

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

推荐答案

"N"&行号&:DI" 不能算出真实地址，因为它缺少该地址后半部分的行号.另外，您应尽可能避免使用Select语句.

"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

这篇关于检查vba模块中的范围是否为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！