How to Delete "Blank" Cells En Masse So They Are Truly Empty?

chasfh

Board Regular
Joined
Dec 10, 2014
Messages
54
Office Version
  1. 365
Platform
  1. Windows
I have come across a problem in which I want the "then" part of a formula to be completely empty.

Here's an example of what I mean. The actual formula here is not important—just the end part of it is:

=IFNA(XLOOKUP($DI2+(EE$5-1901),$G$4:$AK$4,$G1:$AK1),"")

The very end part of this formula is asking to make the cell "blank" if the cell referred to is NA. I may apply this formula to thousands of cells within an array. Afterwards, to make the worksheet nimble, I will copy/paste values so I still see the number that the formula wrought in the appropriate cells, without having to keep the formula in the cell. Any cells without data in them will be "blank". Or at least look empty.

The problem is, any cell I ask to be made blank is not truly empty, but instead has an apostrophe, quote mark, or caret in the cell (depending on cell alignment).

This is fine as far as dependent calculations on the cell are concerned, but the problem comes when I am trying to sort a column of a few thousand rows that has a few hundred of these "blank" cells in them. The sort function reads these cells as being numerically higher than any cell with an actual number in it, so when sorting Z-A, they end up at the top of the column, which creates an obvious problem.

If I can change the status of the cells en masse from "blank" to empty, that will solve the sort problem, but I have not yet found a simple way to do this en masse. The Find and Replace function (CTRL+H) doesn't help because when I try to find any of these "blank" cells, by pressing the space bar in the Find field, the function returns "We couldn't find anything to replace." Obviously, searching for ', ", or ^ doesn't work. And of course, not pressing the space bar in the Find field doesn't even trigger any recognition by the function that I'm trying to use it.

Does anyone recognize this problem and can help? I know I can just go to a "blank" cell, click delete, and that will empty the cell, but I don't want to have to manually find and select every "blank"cell so I can do that. I would rather select an entire array of cells that has both data and "blank" cells in them, and empty out just the blank cells.
 
Last edited:
1. One way, and you'd have to do it for each column. Is to sort so that you have all the "" values together and then delete the values in the cells them (but that is destroying column formula integrity which we've already discussed).

If you are comfortable with VBA you could do one of two routines.
1. Do the formula for that cell in VBA and pass the value into the cells in that column. That way blanks will populate and your problem is solved, but the formula is in VBA and not in the column.
2. To have a macro go down the column and delete any cell with a value of "".
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Rather than Copy/Paste values try using the code
VBA Code:
Range(your range).Value = Range(your range).Value
 
Upvote 0
Rather than Copy/Paste values try using the code
VBA Code:
Range(your range).Value = Range(your range).Value
@MARK858 just an FYI, from my experience, that is only quicker up to a certain amount of cells, about 5000 cells as I recall. ;)
 
Upvote 0
@MARK858 just an FYI, from my experience, that is only quicker up to a certain amount of cells, about 5000 cells as I recall. ;)
if it is the only train in the station to your desitination, you got to get on it.
 
Upvote 0
Here is the mini-sheet. The blank cells have the alignment character in them. Is there any way to make those cells truly empty when I create and paste a formula, if the cell reference is also empty, blank, or otherwise NA?

SAMPLE BLANK EMPTY CELL QUESTION.xlsx
CDEFGHIJKLMNOPQRSTUVWXYZAA
1Name19011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923Averages
2José Abreu128129121121132132
3Bill Abstein757274909174
4Jerry Adair68
5Buster Adams757475758510811911810287110
6Russ Adams8270697471
7Joe Adcock101115124135145141129133135131122118121125140134
8Jesús Aguilar108112
9Willie Aikens12612412512812811380
10Eddie Ainsmith696465708797858790777089
11Gary Alexander8376
12Manny Alexander74757158606566595867757472686559
13Edgardo Alfonzo131118111103938055
14Albert Almora9887726158
15Brian Anderson677679
16Garret Anderson1099595101978360
17Ed Andrews9710210092837363
18Elvis Andrus8190918379851001018575727293
19Nori Aoki103106105101
20Jimmy Archer7168718084869089807473696884
21Oswaldo Arcia1059378
22Nolan Arenado126130131126111108
23Hank Arft9083909184
24Joaquin Arias677886787163
25Randy Arozarena7590132131
26Richie Ashburn127133121119119107104108114111
27Bob Aspromonte75757165858788928687948368636489
28Doug Ault7566
29Jeff Bagwell153171164163165163162154144137130121112149
30Neal Ball887665
31Turner Barber91959979
32Darwin Barney747771646884786868
33John Barry78901029694104104927985
34Kevin Bass1049610311310684
35Frank Baumholtz9178841001051029485747179
36Matt Beaty93107
37Clyde Beck605057
38Erve Beck9998
39Mark Bellhorn103115106101927370
40Cody Bellinger13514311763
41Josh Bell113112
42Carlos Beltrán7595889111112212812111612713012913213413813312211311511193119
43Mike Benjamin716766686445
44Todd Benzinger83918879
45Johnny Berardino917775778285826664635865
46Marty Bergen697371
47Dave Bergman828488971071221078383114114108108998083
48Ken Berry828291105998169
49Sean Berry1211171131087853
50Yuniesky Betancourt868780737676675858
51Wilson Betemit12611510385
52Steve Bieser7676
53Craig Biggio1051018775
54Larry Biittner769287
55Lena Blackburne75757575757576
56John Boccabella7172575141
57Brennan Boesch6249
58Don Bollweg1049676
59Skye Bolt6655
60Julio Borbon777271737575
61Rob Bowen8066
62Jack Boyle8371707266
63Jackie Bradley5564100111978993825367
64Darren Bragg76917763
65Jeff Branson836156687373
66Kitty Bransfield8893981141068577
67Jack Brohamer9489726870
68Lew Brown1241199084798493
69William Brown77848481919686
70Eric Bruntlett8178766148
71Bill Bruton99106106100101102101
72John Buck8483971049181818080
73George Burns81
74Homer Bush686872
75Sal Butera62
76Enos Cabell1119982
77Asdrúbal Cabrera11210998961051141141081029386105
78Willie Calhoun83948481
79Johan Camargo104876565
80Vin Campbell12710591104120120
81Jeimer Candelario989090113123
82John Cangelosi100867674757796123108888279116
83Andy Carey11292819712010385899592
84Fred Carisch7575707168
85Chico Carrasquel88938884898170
86Bill Carrigan71911061029897100105898597
87Fred Carroll134135136143125102
88Frank Carswell76
89Joe Carter1121111101029811111811110090858285105
90Mike Caruso7560567267
91Alberto Castillo524960737773717171
92Jose Castillo6669
93Juan Castro67584750585964
94Starlin Castro9292
95Chris Chambliss11211410187868174
96Bob Chance7873
97Sam Chapman1021041011031069681
98Endy Chavez867375838665718888
99Raul Chavez74766649486270676566
100Cupid Childs148138138138125118128129115978789122
101Harry Chiti847969616578978665606575
102Archi Cianfrocco797986968464
103Alex Cintron94797272666866
104Bud Clancy74
105Brady Clark758181849210710593877890
106Horace Clarke83888271
107Andy Cohen8479
108Darnell Coles757264961061009786687594897672767777
109#N/A
110Dave Collins83107111105909510495898873717492
111Jimmy Collins1149984
112Joe Collins80105124131128122107847278
113Lou Collier586678787875757878
114Pat Collins709812812310283
115Tyler Colvin83908670
116Jeff Conine97107112107105106100888084
117Roger Connor15515214614614513412612110996115
118Bill Conroy757574747778
119Willson Contreras124116111115115111
120Ron Coomer807981827975
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1Expression=ISFORMULA(C1)textNO
C1Expression=ISFORMULA(C1)textNO
A2:C646Expression=ISFORMULA(A2)textNO
D2:Z646Expression=ISFORMULA(D2)textNO
D2:Z646Cell Valuetop 2% bottom valuestextNO
D2:Z646Cell Valuetop 1% valuestextNO
D2:Z646Other TypeColor scaleNO
 
Upvote 0
As @MARK858 pointed out the only way you can do that is with a macro.
If you are happy to select the area first then running this macro will fix it.

VBA Code:
Sub ConvertFormulasToValues()

    Selection.Value = Selection.Value

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top