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]

Hiker,

Sorry but still getting the run time error 1004 - debug line With .Range("A" & c.Row & ":F" & c.Row).SpecialCells(xlCell

 

[hiker95]

skyport,

Hiker,

Sorry but still getting the run time error 1004 - debug line With .Range("A" & c.Row & ":F" & c.Row).SpecialCells(xlCell

Maybe some of the cells are not empty/blank, but may contain special characters.

Be back in a little while.

 

[skyport]

Just double checked and within that range, there are no cells with any formulas or characters in the blank cells. those in that range that are filled only have tel and address, names and nothing more.

 

[Rick Rothstein]

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.

Was this comment directed to me? If so, are you saying the code I posted in Message #3 is filling too many cells?

 

[skyport]

Rick,

Yes that one is to you, welcome back. I was only saying that when I run the code it seems to continue to loop and never end. On a previous code at another thread you did for me, this same problem was solved when you added a pop up window asking for the last row number of data to be included in the processing by the code and that worked out real well. I think the same would eliminate the problem with this code you wrote

 

[Rick Rothstein]

Rick,

Yes that one is to you, welcome back. I was only saying that when I run the code it seems to continue to loop and never end. On a previous code at another thread you did for me, this same problem was solved when you added a pop up window asking for the last row number of data to be included in the processing by the code and that worked out real well. I think the same would eliminate the problem with this code you wrote

Okay, give this (untested, but should work) code a try...

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:G" & LastRow).SpecialCells(xlBlanks)
    Cell.Value = Cell.Address(0, 0)
  Next
End Sub

 

[skyport]

Rick,

Latest version stops the looping or spinning and is instantaneous however, it only populated blank cells in the last column, which are all blank and it only populated G1 and G101 ( the last cell in the range ( 101 was entered into last row window popup)

 

[Rick Rothstein]

Rick,

Latest version stops the looping or spinning and is instantaneous however, it only populated blank cells in the last column, which are all blank and it only populated G1 and G101 ( the last cell in the range ( 101 was entered into last row window popup)

I am not sure what to tell you... I just tested it and it works for me as I expected it to (all blanks cell in A1:G# where # is the entered last row get filled with their address).

 

[hiker95]

skyport,

So that we can get it right on the next try:

Can we see your actual raw data workbook/worksheet with just information in columns A thru F?

You can upload your workbook to (the BLUE link-->) Box Net ,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[skyport]

Rick

Can't even imagine why this would make a difference but I shortened the range to A-F columns from A-G in the code because G really has no data at this time.

With that, the code did work after about 1-2 minutes time.