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]

Did you try the code that brettdj initially posted? I am curious if that works for you.

 

[U412178]

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

I just tried this code again too, and it puts "cancelled" in every single cell too. I guess I'm just too difficult. And I could do it manually but I highly prefer not too, I would like to automate as much as possible.

 

[U412178]

Did you try the code that brettdj initially posted? I am curious if that works for you.

Yea, his code didn't work either... Like I told him, I'm just too difficult I guess

 

[OfficeUser]

I will check it out at home. Right now I am using version 2003. At home I have access to 2003, 2007, and 2010.

 

[brettdj]

Perhaps Excel has got itself confused with what it thinks is your used range. Does this variant which forces a used range re-think work?

Cheers

Dave

Private Sub Workbook_Open()
On Error Resume Next
Dim lngRow As Long
lngRow = ActiveWorksheet.UsedRange.Rows.Count
ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlBlanks) = "CANCELLED"
End Sub

 

[mirabeau]

perhaps you should just try

Sub cancellations()
Dim u
u = ActiveSheet.UsedRange.Replace("", "Cancelled")
End Sub