Making End-down et. al. respect empty cells

[Gates Is Antichrist]

When I paste a column of Access results, and only a few rows contain alphanumeric data, I want to be able to use end-down to find them. I've tried a zillion noncode things to get those empty cells cleared:
B1=A1
B1=if(A1="","",A1)
B1=if(isblank(a1),"",A1)
B1=if(istext(a1),"",A1)
copy column A by value to B
Format columns as text vs. as general

and probably others. However in each case, going end down goes to the bottom of the pasted range, even though only a few scattered cells have alphabetic data.

Now I could probably have macro code to delete contents ... but is there any way to do it directly in Excel?

I just lost Lotus (congratulations, Gates) where this worked as desired every time. At this point, I am really stuck. Even pasting into Notepad and back doesn't do it.

This applies to XL2000 on Win98 and Win2000.

Okay, your turn now, fearless troubleshooters.

 

[zacemmel]

Your name offends me.

 

[Andrew Poulsom]

Remind me what you did in Lotus to delete the cells containing text.

Or you could try Edit, Go To, Special, Constants Text (not Numbers, Logicals or Errors) click OK and press Delete.

 

[Gates Is Antichrist]

Thanks Andrew, but that approach is more work than I was hoping for - i.e. to quickly hop from one value (or clump) to another in a column by going Ctrl-down, Ctrl-down, etc. I'd have to hit delete for each of the 900 cells that don't have data, and require a lot of keystrokes each time (or macro).

Thanks for the interesting idea, though.

As to Lotus, I didn't do anything but paste in the data. End-{direction key} would go right to the first (non-blank? non-empty? ah, there's a question) populated cell.
_________________
______________
Because he is.
This message was edited by Gates Is Antichrist on 2002-09-16 14:01

 

[Andrew Poulsom]

Select the whole column and do the Edit Go to Special Constants Text as I suggested. This selects all the text cells. You only need to press delete once and they are all cleared.

 

[Gates Is Antichrist]

I just realized that Andrew (at least) thought I was trying to delete the cells with alpha data. Sorry for the miscommunication. When I said that I could delete contents via macro, I meant the OTHER cells - the "blank" or "empty" (or whatever they are) cells, so that they would effective be "CLEARED."

That seems to be the essence of the problem. If I had the time to clear every 'blank' cell, end-down would work.

I appreciate your attempts to help out.

 

[Gates Is Antichrist]

I gave up and stuck this in personal.xls. Thanks for anyone who spent any time thinking about it. Thanks, elgringo56, for bringing up TRIM in another thread - it is useful here.

Sub ClearUnusedCells()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If Len(Trim(c.Value)) = 0 Then c.ClearContents
Next c
End Sub

Once this is applied, end-down (et al) works smoothly.