空白单元格列表 [英] Excel List of Blank Cells

问题描述

所以我有一个大的excel表与一堆空单元格在不同的地方。我想要一个易于使用的列表，哪些单元格是空的。我希望制作一个填充有空单元格位置的新工作表。我想拥有这个只是填充我想要的细胞。我保留了工作表中的标题，我将检查并添加一个空白单元格计数，因此我想要列中的以下单元格填充空单元格位置列表。现在我知道我可以使用 = ISBLANK 来测试一个单元格是否为空，但我只关心单元格返回 TRUE 。所以我想我需要一个循环。我想要单元格的位置，所以我可以使用 。为了使这个最可读，我想按列逐列进行。

但是，我想以类似于函数的工作方式填充一个包含此信息的电子表格（我只想将其复制并粘贴到其他单元格和列）。但是它是很清楚我要去需要VBA 。

我的问题是如何创建一个宏来填充我的电子表格一个空单元格的列表？我如何将它应用于单元格？

解决方案

我假设您在sheet1中有数据，我已经使用了范围//范围（A1：c15），但是您可以根据需要定义范围，空白单元格地址将在下一页中公布。

  Sub FindBlank（）
 Dim rng As Range 
 dim i as long 
 
对于每个rng在Sheet1.Range（A1：c15）。SpecialCells（xlCellTypeBlanks）
i = i + 1 
 Sheet2.Cells（i，1）= rng.Address 
 
下一个
 
 End Sub 
  

So I have a big excel sheet with a bunch of empty cells in various locations. I want an easy to work with list of which cells are empty. I was hoping to make a new worksheet that was populated with the locations of the empty cells. I wanted to have this to just populate the cells I want it to. I kept the header from the worksheet I will be checking and added a blank cells count, so I want the following cells in the column to be populated by the list of empty cell locations.

Now I know I can use =ISBLANK to test if a cell is empty or not, but I only care about the cells that return TRUE. So I figure I'll need a loop. And I want the location of the cell so I can use =CELL. And to make this most readable I want to do this on a column by column basis.

But I want to populate a spreadsheet with this information in a manner similar to how functions work (I just want to copy and paste it to other cells and columns). But it's pretty clear that I am going to need VBA.

My question is how can I create a macro to populate my spreadsheet with a list of empty cells? How do I apply it to the cells?

解决方案

I assume you have data in sheet1, I have used sample range// Range("A1:c15") however you can define range as per need and blank cells address will be published in next sheet.

Sub FindBlank()
Dim rng As Range
dim i as long

For Each rng In Sheet1.Range("A1:c15").SpecialCells(xlCellTypeBlanks)
i = i + 1
Sheet2.Cells(i, 1) = rng.Address

Next

End Sub

这篇关于空白单元格列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！