need to fill multiple blank cells in 6 columns with unique numbers

[skyport]

On this one, I am working with data in columns A-F, G is not currently used however, H - AZ are occupied with important info.

Referring only to the data in columns A-F, with a few thousand lines of names and addresses etc, several of the cells contain nothing and are blank.

I need a quick way to assign and fill in each blank cell in that range with a unique number beginning with 1 and then 2, 3, etc etc.

I am hoping this can be done.

 

[skyport]

Actually, regarding the above, the numbers do not have to be 1,2,3 etc. they could be even the value of the cell as long as each blank cell becomes populated with a unique letter,number or combo

 

[Rick Rothstein]

Actually, regarding the above, the numbers do not have to be 1,2,3 etc. they could be even the value of the cell as long as each blank cell becomes populated with a unique letter,number or combo

Give this a try (it fills them with address)...

Sub FillBlanksWithCellAddress()
  Dim Cell As Range
  For Each Cell In Intersect(Columns("A:G"), ActiveSheet.UsedRange).SpecialCells(xlBlanks)
    Cell.Value = Cell.Address(0, 0)
  Next
End Sub

 

[skyport]

Hi again Rick. Beginning to feel like we are family. By the way on that last one earlier this morning, another great part about what you did was making it work by deleting the last entries and working downward and not the first or earlier ones which would have been a problem. Very very well done.

Will try this one now and give immediate feedback

 

[skyport]

I think I know what is preventing it.

We had this same problem before with a previous code you did and the problem was on this end in the sense that further down the sheet, there will always be data floating around here and there that can distract the code.

You had brilliantly solved this on a previous code, that now works great, by simply having a window come up and prompt for the number of the last row with data to be processed under the code. I bet that would work the same on this one and eliminate the problem if you could write it in that way.

 

[hiker95]

skyport,

Here is another macro for you to consider.

Sample raw data in worksheet Sheet1 (you can change the sheet name in the macro):

Excel 2007
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	a	b	c	d	e	f
3	a	b	c	d	e
4	a	b	c	d	e	f
5	a	b	c
6	a	b
7	a
8
9	a	b	c	d	e	f
10		b		d		f
11			c		e
12	a	b	c	d	e	f
13
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	a	b	c	d	e	f
3	a	b	c	d	e	1
4	a	b	c	d	e	f
5	a	b	c	2	2	2
6	a	b	3	3	3	3
7	a	4	4	4	4	4
8	5	5	5	5	5	5
9	a	b	c	d	e	f
10	6	b	6	d	6	f
11	7	7	c	7	e	7
12	a	b	c	d	e	f
13
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ReplaceBlanksWithUniqueNumbers()
' hiker95, 04/22/2015, ME850525
Dim lr As Long, c As Range, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  For Each c In .Range("A2:A" & lr)
    If Application.CountBlank(.Range("A" & c.Row & ":F" & c.Row)) > 0 Then
      n = n + 1
      With .Range("A" & c.Row & ":F" & c.Row).SpecialCells(xlCellTypeBlanks)
        .Value = n
      End With
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReplaceBlanksWithUniqueNumbers macro.

 

[skyport]

Hiker

Good to see another old friend here.

I am getting a runtime error 1004 with a debug pointing to this line: With .Range("A" & c.Row & ":F" & c.Row).SpecialCells(xlCell

 

[hiker95]

skyport,

having a window come up and prompt for the number of the last row with data to be processed under the code.

Let me update the macro to include an InputBox to ask for the last used row in columns A-F.

Be back shortly.

 

[skyport]

ok thanks

 

[hiker95]

skyport,

Here is another macro solution for you to consider that asks for the last used row in columns A thru F:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ReplaceBlanksWithUniqueNumbersV2()
' hiker95, 04/22/2015, ME850525
Dim lr As Long, c As Range, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = InputBox("What is the last used row in columns A thru F?")
  For Each c In .Range("A2:A" & lr)
    If Application.CountBlank(.Range("A" & c.Row & ":F" & c.Row)) > 0 Then
      n = n + 1
      With .Range("A" & c.Row & ":F" & c.Row).SpecialCells(xlCellTypeBlanks)
        .Value = n
      End With
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReplaceBlanksWithUniqueNumbersV2 macro.