NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING

[Stewcrew]

Hello, I need to fill in all blank cells a large data range. My data is
randomley scattered accross several hundred thousand cells and needs to
remain in place. I need to have a text string as a place holder in all blank
cells before importing into quickbooks.

Thank you very much for your help!

Jim

 

[Gary''s Student]

Try:

Sub stew()
For Each r In ActiveSheet.UsedRange
If IsEmpty(r) Then
r.Value = "xx"
End If
Next
End Sub

 

[Stewcrew]

I am getting #NAME? error. I am not sure I am reading your formula
correctley. I have tried entering this several differant ways, with no luck.

 

[Dave Peterson]

Are those blank cells really empty?

If yes.

Select the whole range to fix--extra filled cells won't hurt.
Edit|Goto|special|blanks
notice that the selection changed to just the empty cells.
type your filler text
but hit ctrl-enter instead of enter to fill those empty cells.

 

[Stewcrew]

Yes their is no value or formating in blank cells, but they were
"special pasted" "values only" onto the current sheet.

I am gettin a "no cells were found error"

Jim

 

[Dave Peterson]

They may look blank, but they're not.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

If you need to do this lots, you can record a macro when you do it manually.

=====
After you do this, those cells should really be empty.

Then try the earlier suggestion.

 

[Harlan Grove]

Hello, I need to fill in all blank cells a large data range. My data is
randomley scattered accross several hundred thousand cells and needs to
remain in place. I need to have a text string as a place holder in all blank
cells before importing into quickbooks.

....

Select the entire range, then press [F5] to display the Go To dialog,
click on the Special... button to display the Go To Special dialog,
select Blanks, click the OK button. This will change the selection to
just the blank cells. If your placeholder text string is x, type x,
hold down a [Crtl] key and press [Enter]. This will enter x in all the
selected blank cells.

 

[Gary''s Student]

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To run this macro from the worksheet:
1. touch ALT-F8
2. Run