I have a data dump from a large system.

I have lots of cells with nothing in them, but Excel seems to think they are
not blank. (For example, when I hit Ctrl+Cursur, it scoots right past them).

How can I strip that away?

Thanks

One possibility is apostrophes

if you put a single quote in a and and nothing else, it wil appear empty and
have length zero, but not be blank. Try this little macro:

Sub cleanup()
For Each r In ActiveSheet.UsedRange
If Len(r.Value) = 0 Then
r.Clear
End If
Next
End Sub

--
Gary''s Student - gsnu200760


"RJB" wrote:

I have a data dump from a large system.

I have lots of cells with nothing in them, but Excel seems to think they are
not blank. (For example, when I hit Ctrl+Cursur, it scoots right past them).

How can I strip that away?

Thanks

There are a bazillion tricks to help clean data for Excel-friendliness.
CellView.xla, a nifty add-in from Chip Pearson,
http://www.cpearson.com/Excel/CELLVIEW.ASPX , will show you what invisible
character the cell contains, and ASAP Utilities, www.asap-utilities.com , has
many features that can help.

Vaya con Dios,
Chuck, CABGx3


"RJB" wrote:

I have a data dump from a large system.

I have lots of cells with nothing in them, but Excel seems to think they are
not blank. (For example, when I hit Ctrl+Cursur, it scoots right past them).

How can I strip that away?

Thanks

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

RJB wrote:

I have a data dump from a large system.

I have lots of cells with nothing in them, but Excel seems to think they are
not blank. (For example, when I hit Ctrl+Cursur, it scoots right past them).

How can I strip that away?

Thanks

--

Dave Peterson