从一系列excel细胞动态移除空白 [英] Dynamically remove blanks from a range of excel cells
问题描述
数据
是:
行xy
1 A 77
2
3 B 23
4 A 100
5
而我的新范围是:
行xy
1 A 77
3 B 23
4 A 100
如果空白行结束于数组的末尾,那么可以。到目前为止,我被骗了
你应该使用特殊的单元格方法。无论是vba还是手动。
手动
2007/2010
选择列A
首页标签 - 查找&选择 - 转到特别 - 空白 - 确定
首页标签 - 删除单元格 - 整行 - Ok
VBA p>
Sub DeleteBlanks()
Activesheet.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
如果要保留原始数据,并将范围复制到另一张纸,请尝试以下操作:
Sub DeleteBlanks()
Dim vArray As Variant
'//获取数组的数据
vArray = Sheet1.UsedRange
'//将数据复制到另一个工作表
Sheet2.Range(A1)。调整大小(UBound(vArray,1),UBound(vArray ,2))Value = vArray
'//删除空白
Sheet2.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
公式
真的需要一个公式看看这里:
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. data
is:
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
You should use the special cells method for this. Either with vba or Manually.
Manually
2007/2010
Select column A
Home Tab -Find & Select - Goto Special - Blanks - Ok
Home Tab - Delete Cells - Entire Row - Ok
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:
这篇关于从一系列excel细胞动态移除空白的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!