Excel List of Blank Cells

Question

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?

Answer 1

If you want a list of the cells that are empty, you can use Range().SpecialCells(xlCellTypeBlank):

Sub getEmptyCellAddresses()
Dim rng As Range
Dim ws as Worksheet
Set ws = Sheets("Sheet1") ' CHANGE AS NECESSARY
Set rng = ws.Range("A1:A15").SpecialCells(xlCellTypeBlanks) ' Edit/change range as necessary
ws.Cells(1, 2).Value = rng.Cells.Address ' Change `ws.cells(1, 2)` to whatever destination you like
End Sub

Edit: Ah, beaten by 16 seconds by @RamAnuragi ...but anyways, they're slightly different ways to tackle the question so I'll leave it.

Edit: For funsies, here's another way to put them all in a column, one row per cell...and more, per your comments.

Sub listEmptyCells()
Dim emptyAddresses() As String
Dim i As Long
Dim ws As Worksheet
Dim rng As Range

Set ws = Sheets("Sheet1") ' CHANGE AS NECESSARY
Set rng = ws.Range("A1:A15")

If WorksheetFunction.CountBlank(rng) = 0 Then
    MsgBox ("No empty cells in the range")
    Exit Sub
End If

emptyAddresses() = Split(rng.SpecialCells(xlCellTypeBlanks).Address, ",")

For i = LBound(emptyAddresses) To UBound(emptyAddresses)
    ws.Cells(i + 1, 2).Value = emptyAddresses(i)
Next i

End Sub

Answer 2

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