Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
Try:
Sub stew() For Each r In ActiveSheet.UsedRange If IsEmpty(r) Then r.Value = "xx" End If Next End Sub -- Gary''s Student - gsnu200722 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
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. "Gary''s Student" wrote: Try: Sub stew() For Each r In ActiveSheet.UsedRange If IsEmpty(r) Then r.Value = "xx" End If Next End Sub -- Gary''s Student - gsnu200722 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
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. Stewcrew wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
Stewcrew wrote...
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED TO FILL BLANK CELLS IN DATA WITH TEXT STRING
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 -- Gary''s Student - gsnu200722 "Stewcrew" wrote: 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. "Gary''s Student" wrote: Try: Sub stew() For Each r In ActiveSheet.UsedRange If IsEmpty(r) Then r.Value = "xx" End If Next End Sub -- Gary''s Student - gsnu200722 |
#9
|
|||
|
|||
Quote:
Material properties ABC abc track other millimeter XYZ print drum mech DDD data all data mixed I need the input as below Material Properties ABC abc track other millimeter XYZ print drum mech DDD data all data mixed Appreciate if any one can help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill blank cells with cells above data | Excel Worksheet Functions | |||
shifting of data to fill into blank cells | Excel Discussion (Misc queries) | |||
FILL IN BLANK CELLS | Excel Discussion (Misc queries) | |||
Fill blank cells | Excel Discussion (Misc queries) | |||
How would I fill blank cells with the data from a previous cell? | Excel Discussion (Misc queries) |