0

I have say a column A in a table which have some emty cells like following :

AB345  
AC545

XY675  

VY987  


AC879

I want to to fill the empty cells with unique values by taking an expression and incrementing it all over the empty cells, in a result like :

AB345    
XX1
AC545
XX2  
XY675  
XX3  
VY987  
XX4
XX5  
AC879  

I have tried selecting the empty cells from the table column header, but when I drag the mouse from the first empty cell, it will also overwrite the non empty ones with the incremental values.

How can I achieve this ?

2

For A1 through A100 try this short macro:

Sub FillInTheBlanks()
    Dim rng As Range, r As Range
    Set rng = Range("A1:A100").Cells.SpecialCells(xlCellTypeBlanks)
    i = 1
    For Each r In rng
        r.Value = "XX" & i
        i = i + 1
    Next r
End Sub

Adjust the 100 to suit your needs. Before:

enter image description here

and after:

enter image description here

| improve this answer | |
  • Hey @Gary's, sorry to be off topic, but I'm trying to find an answer that you (I think) posted a while back. All I remember is that it used a binary number, and it struck me as brilliant as many of yours do. :-). The binary number was used somehow as a mask, or as an index to select all combinations from a list, or...? That's about all I remember. Does that ring any bells? – Bandersnatch Mar 22 '18 at 16:18
  • @Bandersnatch I will search.................. – Gary's Student Mar 22 '18 at 16:29
  • I found it. Great answer. – Bandersnatch Mar 23 '18 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.