VBA 在第一个空行停止处理

VBA stop processing at first empty row

提问by manish449

I have vbathat produces a flat text file of the selected column.

我有生成所选列的纯文本文件的vba。

The issue is that the process takes a while because usually the column letter is clicked and the whole column is highlighted including all the unused cells.

问题是该过程需要一段时间，因为通常单击列字母并突出显示整列，包括所有未使用的单元格。

How can i get the macro to stop processing when it finds the first empty row?

我怎样才能让宏在找到第一个空行时停止处理？

Here is my code.

这是我的代码。

Sub testlist()
Open "C:\Users\gaum\Desktop\Work\NCL\testlist.lst" For Output As #1
For NR = 1 To Selection.Rows.Count
For NC = 1 To Selection.Columns.Count
ExpData = Selection.Cells(NR, NC).Value
If IsNumeric(ExpData) Then ExpData = Val(ExpData)
If IsEmpty(Selection.Cells(NR, NC)) Then ExpData = ""
If NC <> NumCols Then
If Not ExpData = "FilePath" Then Print #1, ExpData
End If
Next NC
Next NR
Close #1
End Sub

Also am i able to get the macro to produce the output if i have multiple selections i.e ctrl and left click various cells, it currently only outputs the first highlight.

如果我有多个选择，即 ctrl 和左键单击各个单元格，我也可以让宏产生输出，它目前只输出第一个突出显示。

Many Thanks

非常感谢

采纳答案by psubsee2003

Since you asked 2 separate questions, I will address them both separately.

由于您提出了 2 个不同的问题，因此我将分别解决它们。

The easiest way to stop processing when you encounter a blank row is to add a check before your 2nd For..Nextloop. The issue is how to check. The simplest way to check if an entire range is empty is to use the CountAworksheet function.

遇到空行时停止处理的最简单方法是在第二个For..Next循环之前添加检查。问题是如何检查。检查整个范围是否为空的最简单方法是使用CountA工作表函数。

If WorksheetFunction.CountA(Range(NR & ":" & NR).EntireRow) = 0 Then Exit For

The above will basically use the worksheet function CountAand count the number of cells within the range that are not blank (using CountAis important here as the Countworksheet function will only count numeric cells and not non-numeric ones, whereas CountAwill count anything except blanks. The other advantage you get by using the WorksheetFunctionobject is you can adjust the Rangeobject as you need if you only want to check a few columns and the not the entire row by just specifying the specific Rangeand not using .EntireRow.

以上将基本上使用工作表函数CountA并计算范围内非空白单元格的数量（使用CountA在这里很重要，因为Count工作表函数将只计算数字单元格而不是非数字单元格，而CountA将计算除空白之外的任何内容。使用WorksheetFunction对象获得的另一个优势是，Range如果您只想通过指定特定Range而不是使用.EntireRow.

---

---

The next question is how to deal with multiple selected ranges. There is another member of the Selectionclass called Areas, which should given you the functionality you need. Areasis a collection that has the ranges for each individual selection range you make.

下一个问题是如何处理多个选定的范围。Selection类的另一个成员称为Areas，它应该为您提供所需的功能。 Areas是一个集合，其中包含您所做的每个单独选择范围的范围。

You can reference each selection range independently by using the 1-based index of the selection:

您可以使用选择的从 1 开始的索引来独立引用每个选择范围：

NumAreaRows = Selection.Areas(1).Rows.Count 'gets the number of rows in the first selected range
NumAreaCols = Selection.Areas(2).Columns.Count 'gets the number of columns in the second selected range

---

---

So you could put both all together into your solution:

因此，您可以将两者都放在您的解决方案中：

Sub testlist()
    Open "C:\Users\gaum\Desktop\Work\NCL\testlist.lst" For Output As #1

    For NA = 1 To Selection.Areas.Count
        For NR = 1 To Selection.Areas(NA).Rows.Count

            If WorksheetFunction.CountA(Range(NR & ":" & NR).EntireRow) = 0 Then Exit For

            For NC = 1 To Selection.Areas(NA).Columns.Count
                ExpData = Selection.Areas(NA).Cells(NR, NC).Value
                If IsNumeric(ExpData) Then ExpData = Val(ExpData)
                If IsEmpty(Selection.Areas(NA).Cells(NR, NC)) Then ExpData = ""
                If NC <> NumCols Then
                    If Not ExpData = "FilePath" Then Print #1, ExpData
                End If
            Next NC
        Next NR
    Next NA
    Close #1
End Sub

The placement of the CountAfunction and the Exit Forstatement here allows you to loop through each selected range independently and it won't exit completely if you have a blank row in one of the ranges.

此处的CountA函数和Exit For语句的位置允许您独立地循环遍历每个选定的范围，如果其中一个范围中有空行，则不会完全退出。

回答by brettdj

Given this process takes a while, you would be better off going beyond stopping at a blank cell, and removing the inefficient range loop altogether. The code below

鉴于此过程需要一段时间，您最好不要在空白单元格处停止，并完全消除低效的范围循环。下面的代码

• Uses a variant array rather than range
• removes the redundant two-step IFtest (if ExpDatais numeric it cannot alsobe "FilePath")

• 使用变体数组而不是范围
• 去除多余的两步IF试验（如果ExpData是数字它不能也为"FilePath"）

code

代码

Sub testlist()
Dim X
Dim lngCnt As Long
X = Selection
If IsEmpty(X) Then Exit Sub
Open "C:\Users\gaum\Desktop\Work\NCL\testlist.lst" For Output As #1
For lngCnt = 1 To UBound(X)
If Len(X(lngCnt, 1)) = 0 Then Exit For
If IsNumeric(X(lngCnt, 1)) Then Print #1, Val(X(lngCnt, 1))
Next
Close #1
End Sub