vba 从一系列 Excel 单元格中动态删除空白
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7068782/
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
Dynamically remove blanks from a range of excel cells
提问by Zach
I have a named range of data, called 'data'. I'm trying to find a formula or array formula that will return data in a new range of cells, but will all the blank rows missing.
我有一个命名的数据范围，称为“数据”。我正在尝试找到一个公式或数组公式，该公式或数组公式将在新的单元格范围内返回数据，但会丢失所有空白行。
i.e. datais:
即data：
row x y
1 A 77
2
3 B 23
4 A 100
5
And my new range is:
我的新范围是：
row x y
1 A 77
3 B 23
4 A 100
It's ok if the blank rows end up at the end of the array. So far I am stumped
如果空白行出现在数组的末尾，那也没关系。到目前为止，我很难过
回答by Reafidy
You should use the special cells method for this. Either with vba or Manually.
为此，您应该使用特殊单元格方法。使用 vba 或手动。
Manually
手动
2007/2010
2007/2010
Select column A
Home Tab -Find & Select - Goto Special - Blanks - Ok
Home Tab - Delete Cells - Entire Row - Ok
选择列 A
主页选项卡 - 查找和选择 - 转到特殊
选项 -空白 - 确定主页选项卡 - 删除单元格 - 整行 - 确定
VBA
VBA
Sub DeleteBlanks()
Activesheet.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
If you want to leave the original data intact and copy the range to another sheet try something like:
如果您想保持原始数据不变并将范围复制到另一个工作表，请尝试以下操作：
Sub DeleteBlanks()
Dim vArray As Variant
'// Get an array of your data
vArray = Sheet1.UsedRange
'// Copy the data to another sheet
Sheet2.Range("A1").Resize(UBound(vArray, 1), UBound(vArray, 2)).Value = vArray
'// Delete blanks
Sheet2.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
FORMULA
公式
If you really need a formula take a look here:
如果你真的需要一个公式看看这里：
回答by Chris
Why not just use a filter where you exclude blanks from the appropriate column(s)?
为什么不直接使用过滤器从适当的列中排除空白？
Alternatively, you could create an additional flag column which designates each row to be included based on your blank criteria (eg, IF(OR(X="",Y=""),0,1)) and use it to filter your data.
或者，您可以创建一个额外的标志列，根据您的空白条件（例如，IF(OR(X="",Y=""),0,1)）指定要包含的每一行，并使用它来过滤您的数据。
回答by Nicola Cossu
I like Chris's advice. This is a vba approach. Try it on a copy of your file.
我喜欢克里斯的建议。这是一种 vba 方法。在您的文件副本上尝试一下。
Sub delete_empty_rows()
Dim last As Long, i As Long
last = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = last To 1 Step -1
If Application.CountA(Range("A" & i).EntireRow) = 0 Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub
