Making End-down et. al. respect empty cells

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

Forum statistics

Threads
1,148,280
Messages
5,745,829
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top