Thread: Create list of blank fields

Hi,
See attached mock-up.
I have a database style list that contain blanks in some fields
I wish to extract the ID and the field name (top row of list) to create a list of those blanks by ID, e.g.

ID Field
1 Field 3
1 Field 5
3 Field 2
3 Field 7

My list is over 1000 rows long and I just want to be able to quickly identify the blanks by ID

Thanks

As per your example:

Sub getBlanks(TopLeft As Range, listRng As Range)
'TopLeft is the top left cell of the table
'Listrng is the cell where the list should start


Dim ocell As Range
Dim i As Integer

For Each ocell In TopLeft.CurrentRegion.SpecialCells(xlCellTypeBlanks)
    listRng.Offset(i, 0).Value = ocell.Row - TopLeft.Row
    listRng.Offset(i, 1).Value = "Field " & ocell.Column - TopLeft.Column
    i = i + 1
Next ocell

End Sub
Sub test()
    getBlanks Sheet1.Range("C3"), Sheet1.Range("C30")
End Sub

or:

Sub snb()
  j = 0
  ReDim sn(Cells(3, 3).CurrentRegion.SpecialCells(4).Count - 1, 1)

  For Each cl In Cells(3, 3).CurrentRegion.SpecialCells(4)
    sn(j, 0) = cl.Offset(, 3 - cl.Column)
    sn(j, 1) = cl.Offset(3 - cl.Row)
    j = j + 1
  Next
    
  Cells(18, 1).Resize(UBound(sn) + 1, 2) = sn
End Sub

NB. the main difference is that there will be written only once into the worksheet.

or even better, a combination:

Sub getBlanks(TopLeft As Range, listRng As Range)
'TopLeft is the top left cell of the table
'Listrng is the cell where the list should start

Dim ocell As Range
Dim i As Integer
Dim arr

ReDim arr(TopLeft.CurrentRegion.SpecialCells(xlCellTypeBlanks).Count - 1, 1)
For Each ocell In TopLeft.CurrentRegion.SpecialCells(xlCellTypeBlanks)
    arr(i, 0) = ocell.Row - TopLeft.Row
    arr(i, 1) = "Field " & ocell.Column - TopLeft.Column
    i = i + 1
Next ocell

listRng.Resize(UBound(arr) + 1, 2).Value = arr


End Sub