用于选择非空单元格的 Excel VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39967118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA code to select non empty cells
提问by k.dkhk
In Excel, let's I have data in B2 to B7 and C2 to C7 . In VBA I can write a macro to select it:
在 Excel 中，让我在 B2 到 B7 和 C2 到 C7 中有数据。在 VBA 中，我可以编写一个宏来选择它：
Sub Macro1()
Range("B2:C7").Select
End Sub
How do I rewrite the code so that it chooses automatically the cells that are non-empty? If I delete the data in cell B7 and C7 then I want the macro to select only Range(B2:C6) And if I add data to Cell B8 and C8 then I want the macro to choose Range(B2:C8).
如何重写代码以使其自动选择非空单元格？如果我删除单元格 B7 和 C7 中的数据，那么我希望宏只选择范围（B2：C6），如果我将数据添加到单元格 B8 和 C8，那么我希望宏选择范围（B2：C8）。
My data will always start a B2,C2 and I will not have any free space between data.
我的数据将始终以 B2、C2 开头，并且数据之间没有任何可用空间。
回答by L.Dutch - Reinstate Monica
your data always start at B2,C2 and has no empty cell inbetween? If so you can set a variable to be the "last filled in row"
您的数据总是从 B2,C2 开始并且中间没有空单元格？如果是这样，您可以将变量设置为“最后填充的行”
lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("B2:C" & lastRow).Select
and define the range from B2 to the C"last row"
并定义从 B2 到 C“最后一行”的范围
回答by Gary's Student
Use a loop:
使用循环：
Sub qwerty()
Dim rng As Range, r As Range, rSel As Range
Set rng = Range("B2:C7")
Set rSel = Nothing
For Each r In rng
If r.Value <> "" Then
If rSel Is Nothing Then
Set rSel = r
Else
Set rSel = Union(rSel, r)
End If
End If
Next r
If Not rSel Is Nothing Then rSel.Select
End Sub
If you want to expand the area being tested, use:
如果要扩展被测试的区域，请使用：
Range("B2:C7").CurrentRegion
回答by Maksim Sych
Use the 'SpecialCells' function of Selection object
使用 Selection 对象的“SpecialCells”功能
Sub Macro1()
Range("B2:C7").Select
For Each self in Selection.SpecialCells(xlCellTypeConstants)
Debug.Print(self)
Next
End Sub
