How to delete contents of all cells with a formula that results in an empty space

[yslguru]

PROBLEM: How to delete the contents of cells so that keyboard short cuts like SHIFT+CTL+DOWN ARROW work normally, like as if the cells were truly empty.

If cells A1 and B1 contain the values Hello & World respectively and the rest of the cells in columns A & B are empty until I get to row 10 where A10 and B10 contain the values Goodbye & World respectively, then if I select cell A1 AND press CTL+SHIFT+RightArrow key cells A1: B1 are selected. If I keep CFTL+SHIFT held and press the Down Arrow key the selected range goes to A1:B10 because every cell between A2:B9 are empty. If Cell B5 had something in it then the select range would instead be A1:B5. I am detailing this so my request makes sense; why I'm asking what I am.

I have around 200K+ rows where columns A & B previously contained an IF() formula that would either return the contents of another cell or "" like this =IF(A1<>"",A1,"") . I selected all the cells in columns A & B and did a COPY and PASTE with VALUES ONLY to then get rid of the formulas which worked however the cells where the IF() returned "" still contained something so that the above keyboard short cuts don't work. I call these ghost cells because they don't really have anything in them but excel acts like they do.

If I manually select a range of these ghost cells and do a delete then the keyboard shortcuts work but I need to do that for 200K+ rows and it's not contiguous meaning I can't just select A1:B200000 and press delete because some rows within that range do have values.

I need some way to locate all these ghost cells so I can then do a single delete to get rid of whatever it is in those cells that prevents the keyboard shortcuts from working. If no excel functionality can do it I'm open to doing it using VBA in Excel. I do hope I have accurately explained the problem.

Thanks in advance

 

[Peter_SSs]

I call these ghost cells because they don't really have anything in them

That is not so. They contain a zero length string (that was previously returned by the IF formula)

Try this with a copy of your data (I'm assuming that on each row both A & B will be blank or both will be non-blank)

1. Select the entire columns A:B (or just col B would do)
2. Apply AutoFilter
3. Filter column B for Blanks
4. Select the first visible cell in col A below the headings then ..
5. Then Ctrl+Shift+Right arrow then Ctrl+Shift+Down arrow
6. Press the Delete key
7. Remove the AutoFilter
8. Test your original selection method.

 

[Alex Blakenburg]

Are you familiar with the VBA Editor ?
Alt+F11 to get into the editor.
If you can't see the immediate box use Ctrl+G
Copy the below into the immediate box and hit enter

range("A1").CurrentRegion.resize(,2).Value = range("A1").CurrentRegion.resize(,2).Value

Anything in columns A or B that was "" should now be empty.

 

[yslguru]

That is not so. They contain a zero length string (that was previously returned by the IF formula)

Try this with a copy of your data (I'm assuming that on each row both A & B will be blank or both will be non-blank)

1. Select the entire columns A:B (or just col B would do)
2. Apply AutoFilter
3. Filter column B for Blanks
4. Select the first visible cell in col A below the headings then ..
5. Then Ctrl+Shift+Right arrow then Ctrl+Shift+Down arrow
6. Press the Delete key
7. Remove the AutoFilter
8. Test your original selection method.

Worked perfectly. easily and painlessly. Thanks

 

[Peter_SSs]

You’re welcome. Thanks for the confirmation.

 

[yslguru]

What I appreciate is the simplicity of the solution. There's definitely a number of ways to achieve the same end result but I prefer the one that is closest to teh end user experience meaning less use of things like macros or the VBA editor although I am very familiar with both.

 

[Alex Blakenburg]

Thanks for the explanation.
Simple depends on the situation if both A & B alway contain "" on the same row then you would only need to apply the filter once.
If A & B are not always in sync in terms of containing "" then you would need to run through the filter process twice in which case using Text to Columns on Column A and the on Column B might be simpler.

The advantage of the VBA code is that in a single line you could do the both steps ie your first step is to copy paste values and the 2nd is convert "" to empty/blank. The code would do both. You could then eliminate a quite lengthy explanation of both steps and have a single button click take care of it.

 

[yslguru]

Thanks for the explanation.
Simple depends on the situation if both A & B alway contain "" on the same row then you would only need to apply the filter once.
If A & B are not always in sync in terms of containing "" then you would need to run through the filter process twice in which case using Text to Columns on Column A and the on Column B might be simpler.

The advantage of the VBA code is that in a single line you could do the both steps ie your first step is to copy paste values and the 2nd is convert "" to empty/blank. The code would do both. You could then eliminate a quite lengthy explanation of both steps and have a single button click take care of it.

B/C I was the one who copied and pasted the formula I knew the cells would all be of the same contents