如何计算宏中的非空单元格 [英] how to count non empty cells in macro

问题描述

我想出了一个代码来打开多个工作簿。代码是

Hi i have come up with a code to open multiple workbooks. The code is

Sub OPEN_hari()
Dim r As Long
For r = 1 To 10
    Workbooks.Open Filename:=Sheet2.Cells(r, 1).Value
Next r
End Sub

但是这里的问题是，如果只有3个单元格（（1,1）（2,1）（3,1））具有工作簿文件的路径，则会抛出一条错误消息，指出库伦特打开文件。
如何使此宏仅打开提到的文件而没有错误消息。
作为我的初学者，我们将不胜感激

But the problem here is if only 3 cells ((1,1) (2,1) (3,1)) has the path of the workbook files, an error message is thrown that coulndt open "" files. How can i make this macro to open only the files that are mentioned without error message. As i am beginner in this any help is highly appreciated

推荐答案

有两种方法可以满足您的需求：

There're two ways of getting what you need:

1. 运行无限循环，检查单元格的空度：

1. Run an infinite loop, checking the emptiness of a cell:

Dim i as Long: i = 0 

While Not IsEmpty(Sheet1.Cells(i + 1, 1)) 'assuming the value is in Column A
    'your code
    i = i + 1 
Wend

获取已使用的行数，但是存在一个单元格可能已被使用但现在没有数据的风险，因此，我要包括支票：

Get the number of used rows, however there's a risk that a cell might have been used, but is have no data now, so I'm including a check:

Dim ur as Long: ur = Sheet1.UsedRange.Rows.Count
Dim i as Long

For i = 0 to (ur - 1)
     If LenB(Sheet1.Cells(i + 1, 1).Value) > 0 then
        'your code
     End If 
Next i

这篇关于如何计算宏中的非空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！