Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing "dummy" cell entries - zero length
Hi
I have done some searching of the archive but not successfully for the following problem. I have datafiles produced from an accounting ledger system and <null cells are produced for columns that have no data. The null cells are zero length strings that show up as text items i.e. ISTEXT(A1) is TRUE, but equally LEN(A1) is 0 and ISBLANK(A1) is FALSE. When in Excel itself, simply editing the cell makes it revert to a true empty (blank) cell. What is the most efficient way of identifying the cells in VBA and then removing them, please? (Ideally, I don't want to test each cell). The cells can be anywhere in the sheet data but are often in whole columns. Thanks Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing "dummy" cell entries - zero length
Hi
many thanks - will try it out bw Tim "JMay" wrote in message ... Back up Your FILE FIRST !! & In a standard module paste in: Sub foo() For Each c In UsedRange If c.Value = "" Then c.ClearContents End If Next c End Sub Make sure it does what you want it to before Re-Saving your File !!! HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing "dummy" cell entries - zero length
I like this way to clean up that type of "blank" cell.
select the range (or the whole sheet) edit|replace what: (leave blank) with: $$$$$ (some unique value--not used!) Replace all Then reverse it: edit|replace what: $$$$$ (that same value) with: (leave blank) Replace all ======== You can also get this detritus(?) when you have a formula that evaluates to "" and is converted to values. =if(a17,"ok","") Tim Childs wrote: Hi I have done some searching of the archive but not successfully for the following problem. I have datafiles produced from an accounting ledger system and <null cells are produced for columns that have no data. The null cells are zero length strings that show up as text items i.e. ISTEXT(A1) is TRUE, but equally LEN(A1) is 0 and ISBLANK(A1) is FALSE. When in Excel itself, simply editing the cell makes it revert to a true empty (blank) cell. What is the most efficient way of identifying the cells in VBA and then removing them, please? (Ideally, I don't want to test each cell). The cells can be anywhere in the sheet data but are often in whole columns. Thanks Tim -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing "dummy" cell entries - zero length
Hi Dave
Many thanks for this - I will try this out Looks very neat and, I suspect, very fast which is important u've not lost your edge!!! THANKS Tim "Dave Peterson" wrote in message ... I like this way to clean up that type of "blank" cell. select the range (or the whole sheet) edit|replace what: (leave blank) with: $$$$$ (some unique value--not used!) Replace all Then reverse it: edit|replace what: $$$$$ (that same value) with: (leave blank) Replace all ======== You can also get this detritus(?) when you have a formula that evaluates to "" and is converted to values. =if(a17,"ok","") Tim Childs wrote: Hi I have done some searching of the archive but not successfully for the following problem. I have datafiles produced from an accounting ledger system and <null cells are produced for columns that have no data. The null cells are zero length strings that show up as text items i.e. ISTEXT(A1) is TRUE, but equally LEN(A1) is 0 and ISBLANK(A1) is FALSE. When in Excel itself, simply editing the cell makes it revert to a true empty (blank) cell. What is the most efficient way of identifying the cells in VBA and then removing them, please? (Ideally, I don't want to test each cell). The cells can be anywhere in the sheet data but are often in whole columns. Thanks Tim -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
In Excel, show 0.00 for "zero" entries, but "-" if no entries | Excel Discussion (Misc queries) | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |