need VBA to fill in blank cells with the cell value or unique number

[skyport]

Hoping someone can help with this one.

I need a VBA that can locate every blank cell in columns A-F and fill in each blank cell with the cell value or any other unique number so that every cell shows a different value rather than the blank cells all showing the same value.

I have currently been using the following but it doesn't always work:

Sub FillBlanksWithCellAddress()
Dim LastRow As Variant, Cell As Range
LastRow = Application.InputBox("What is the last row number?", Type:=1)
If LastRow = "False" Then Exit Sub
For Each Cell In Range("A1:F" & LastRow).SpecialCells(xlBlanks)
Cell.Value = Cell.Address(0, 0)
Next
End Sub

 

[CWatts]

I don't know if I'm understanding what you need or if I'm grossly over-simplifying, but you can just highlight your data and run this:

Sub FillErUp()
    Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=ADDRESS(ROW(),COLUMN())"
End Sub

It would certainly be faster than a For loop. And you can use it as is and manually highlight or do what you need to get your selection.

 

[CWatts]

Here's another option if you can't leave formulas in your data. It's a for loop, but only loops through your blanks rather than all cells.

Sub FillErUp()
    ActiveWorkbook.Names.Add Name:="Blanks", RefersToR1C1:=Selection.SpecialCells(xlCellTypeBlanks)
    
    Range("Blanks").Select
    
    For Each c In Selection
        c.Value = Chr(c.Column + 64) & c.Row
    Next c
End Sub

 

[skyport]

Hi CWatts Thanks much for helping. First program worked perfectly and fast. Second one did not seem to do anything. If you care to play with it a bit, I can certainly give you feedback. However, first one does get the job done.

 

[CWatts]

If the first one is sufficient for your needs and the problem is solved, then that's good enough for me. Enjoy!

 

[skyport]

thanks again