vba 选择电子表格中的非空白行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14228014/
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
Select non-blank rows in spreadsheet
提问by user1959930
I am going to try and keep this as short as I can and still explain adequately, here goes :)
我将尽量保持简短，并且仍然充分解释，这里是:)
I have searched forums, my VBA literature, and cannot find a way to do what I'm trying.
我搜索了论坛，我的 VBA 文献，但找不到做我正在尝试的方法。
I have a spreadsheet with rowTotal >= 60 rows. The rows either have text data in cells of each column, or the rows are blank with a pattern and colorindex set.
我有一个 rowTotal >= 60 行的电子表格。这些行要么在每列的单元格中包含文本数据，要么这些行是带有图案和颜色索引集的空白行。
I need a macro to select all non-blank rows.
我需要一个宏来选择所有非空白行。
I first tried looping through the cells of column A (if a cell in column A has text data, then its row should be selected), checking if activecell.value<> empty.
我首先尝试遍历 A 列的单元格（如果 A 列中的单元格有文本数据，则应选择其行），检查activecell.value<> 是否为空。
Here's the jist (mix of pseudocode & code):
这是 jist（伪代码和代码的混合）：
Range("A1").Select
loop to end
if activeCell.value <> empty then
stringVar = stringVar + cstr(activeCell.row) + ":" + cstr(activeCell.row) + ","
end if
end loop
stringVar = Left(stringVar, (Len(stringVar) - 1))
Range(stringVar).Select
If I have total 10 rows with rows 2 and 8 having data,
stringVarwould resolve to this:"2:2, 8:8".Range(stringVar).Selectwould have same result as writingRange("2:2, 8:8").Select.If the number of rows to be in the range is <= 45, this works no problem. However, as soon as the number of rows with data in them exceeds 45, the code fails on
Range(stringVar).Select.
如果我总共有 10 行，其中第 2 行和第 8 行有数据，
stringVar则将解析为："2:2, 8:8".Range(stringVar).Select将与 write 产生相同的结果Range("2:2, 8:8").Select。如果该范围内的行数 <= 45，则没有问题。但是，一旦其中包含数据的行数超过 45，代码就会失败
Range(stringVar).Select。
I tried the macro recorder and it gets around this by using the Unionmethod. And so I thought, "self, you can get this done with Union(). hooray MacroRecorder." But alas, my joy was remiss.
I was thinking I could split the one large string into 1 or more strings; each of these smaller strings would be under the 45 limit mentioned above. Then I can use Union() to group all the ranges (these smaller strings) together into the one desired range.
我尝试了宏记录器，它通过使用该Union方法解决了这个问题。所以我想，“自我，你可以用 Union() 来完成这件事。万岁 MacroRecorder。” 但是，唉，我的喜悦是失职了。
我在想我可以将一根大弦分成 1 根或多根弦；这些较小的字符串中的每一个都将低于上述 45 个限制。然后我可以使用 Union() 将所有范围（这些较小的字符串）组合到一个所需的范围内。
However, I would have to "build" my Union() code in real time during code execution, after I knew how many of these 45> stringsI had.
但是，在我知道这些45> 字符串中有多少个之后，我必须在代码执行期间实时“构建”我的 Union() 代码。
Anyone know how to take a worksheet and select just rows that contain data; which amounts to having a range of non-contiguous rows where more than a count of 45 rows are selected.
任何人都知道如何获取工作表并仅选择包含数据的行；这相当于具有一系列非连续行，其中选择了超过 45 行的计数。
回答by brettdj
No need for loops - use SpecialCells
不需要循环 - 使用 SpecialCells
For column A only use:
对于 A 列仅使用：
Set rng1 = Columns("A").SpecialCells(xlCellTypeConstants).EntireRow
instead.
反而。
Sub QuickSet()
Dim rng1 As Range
On Error Resume Next
Set rng1 = Cells.SpecialCells(xlCellTypeConstants).EntireRow
On Error GoTo 0
If Not rng1 Is Nothing Then
MsgBox "Your working range is " & rng1.Address(0, 0)
Else
MsgBox "No constants found"
End If
End Sub
回答by mkingston
I first suggest you try using Autofilter. If you're using Excel 2010 (and prob 2007, but I can't check) this is as simple as selecting your data, choosing the "Data" tab, then clicking Filter. Using the drop-down box in your first column, deselect "blanks".
我首先建议您尝试使用自动过滤器。如果您使用的是 Excel 2010（和 prob 2007，但我无法检查），这就像选择数据、选择“数据”选项卡、然后单击过滤器一样简单。使用第一列中的下拉框，取消选择“空白”。
The exact same functionality exists in Excel 2003, under the Data/Filter menu option. I can't really remember it all that well, though; you'll have to experiment, or Google it.
Excel 2003 中的“数据/筛选器”菜单选项下存在完全相同的功能。不过，我真的记不清了。你必须试验，或者谷歌它。
If that doesn't work:
如果这不起作用：
Sub it()
Dim cell As Range
Dim selectRange As Range
For Each cell In ActiveSheet.Range("A:A")
If (cell.Value <> "") Then
If selectRange Is Nothing Then
Set selectRange = cell
Else
Set selectRange = Union(cell, selectRange)
End If
End If
Next cell
selectRange.Select
' selectRange.EntireRow.Select 'If you want to select entire rows
End Sub
回答by Julieblue
Just used this code and it worked a treat - been tracking all other excel forums but couldn't find anything that was as simplified.
刚刚使用了这段代码，它工作得很好 - 一直在跟踪所有其他 excel 论坛，但找不到任何简化的东西。
I also added that the selected rows were copied and pasted to the next blank row in another sheet, if anyone finds this useful.
我还补充说，如果有人觉得这有用的话，选定的行会被复制并粘贴到另一个工作表中的下一个空白行。
Sub copypaste1()
'Find rows that contain any value in column A and copy them
Dim cell As Range
Dim selectRange As Range
For Each cell In ActiveSheet.Range("A:A")
If (cell.Value <> "") Then
If selectRange Is Nothing Then
Set selectRange = cell
Else
Set selectRange = Union(cell, selectRange)
End If
End If
Next cell
selectRange.EntireRow.Select
selectRange.EntireRow.Copy
'Paste copied selection to the worksheet 'mega' on the next blank row
Sheets("mega").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
End Sub
