Populate empty cells

[U412178]

I have a spreadsheet that contains blank cells in a row (the entire row is not blank, just random cells). Is there a marco or formula I can use to automatically fill-in the random empty cells upon opening the workbook? I would like the blank cells to show "CANCELLED".

Thank you for your help in advance!

 

[OfficeUser]

Insert this into ThisWorkbook module

Private Sub Workbook_Open()
    For Each c In Range("A1:H40")
        If c.Value = "" Then c.Value = "CANCELLED"
    Next c
End Sub

Change A1:H40 to the range of your data. You could search all cells on a worksheet but that could take time.

 

[brettdj]

If the cells are truly blank then you can use the SpecialCells shortcut (also available manually without code, via F5 - Goto Special - Blanks and then type in "CANCELLED" in the edit bar and hit Cntl & Enter together)

This code goes in the ThisWorkbook module

It runs on the used range portion of the first worksheet

hth

Dave

Private Sub Workbook_Open()
On Error Resume Next
ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlBlanks) = "CANCELLED"
End Sub

 

[vbsjive]

This should work.

Highlight the area you want to replace blank cells.

Then hit

Hit ctrl + H

Leave the top find what? bar blank

and put "cancelled" in the replace with bar.

 

[U412178]

Thank you everyone!

I used the code that OfficeUser supplied, but is there a way to tell excel to run the code to only the end of the data set instead of having to put in the actual cells? This spreadsheet has rows added to it every few days and I would like the code to just have excel auto populate the empty cells when new rows are inserted.

Any help is greatly appreicated! Thank you!!

 

[OfficeUser]

Give this a shot:

Private Sub Workbook_Open()
    If Range("A1").Value = "" Then
    Range("A1").Value = "CANCELLED"
    End If
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlBlanks) = "CANCELLED"
End Sub

 

[U412178]

Hello OfficeUser,
Thank you!! But that code replaced every cell with "CANCELLED", not just the empty cells. Any other thoughts?

 

[OfficeUser]

Really?!? I just opened a new worksheet, placed the code into ThisWorkbook module, randomly placed data into my worksheet, comprised of letters and numbers, and closed it. Upon opening the sheet, the cells that were blank prior now said CANCELLED, and the ones I had put data into were still as they were.

What version excel are you running?

 

[brettdj]

Thank you everyone!

I used the code that OfficeUser supplied, but is there a way to tell excel to run the code to only the end of the data set instead of having to put in the actual cells? This spreadsheet has rows added to it every few days and I would like the code to just have excel auto populate the empty cells when new rows are inserted.

Any help is greatly appreicated! Thank you!!

Did you try my suggestion?

Cheers

Dave

 

[U412178]

Really?!? I just opened a new worksheet, placed the code into ThisWorkbook module, randomly placed data into my worksheet, comprised of letters and numbers, and closed it. Upon opening the sheet, the cells that were blank prior now said CANCELLED, and the ones I had put data into were still as they were.

What version excel are you running?

I'm running 2010 excel. I just tried the code again and the code put "cancelled" in every cell.