Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Macro to Find and Delete Blank Strings produced by Formulas

without comments

Some day I’ll write a “Top 10 list” about the problems with Excel, and workarounds, and when I do, this post will be on the list! While I know the title of this post sounds obscure, I run into a problem ALL THE TIME with Excel, that often I have a formula that if there is an error I want it to resolve to being blank, and I can kind of do this with =iferror(formula,””)  And while this looks blank on the screen, and many formulas will treat it as being blank, unfortunately both PivotTables and charts will not!  For years, I have solved this problem by manually going to each cell that has a blank, and pressing delete. (Because find and replace can’t do this!!)  But that wastes a lot of time, so today, I finally decided to take the time to create a macro to fix this, because in my searching on the Internet, I never found one.

So without further adieu, here is the code that I recommend you add to your PERSONAL.XLSB so that you can run this Macro whenever you want to delete all empty strings (which could also be considered changing empty strings to nulls):

Sub Find_and_Delete_Empty_Strings()
' Find_and_Delete_Empty_Strings Macro
' This macro finds and deletes cells that produce a blank result as part of a formula, to stop them from causing issues with PivotTables and Charts
' Keyboard Shortcut: Ctrl+Shift+D
' This macro does some "fancy stuff" with disabling calculations to improve the speed
Dim OriginalCalculationState As Integer
Dim Iteration As Long
Iteration = 0

OriginalCalculationState = Application.Calculation
Application.Calculation = xlCalculationManual

CellCount = ActiveSheet.UsedRange.Columns.Count * ActiveSheet.UsedRange.Rows.Count

For Each Cell In ActiveSheet.UsedRange.Cells
 Iteration = Iteration + 1
 Application.StatusBar = "Deleting All Empty Strings/Text. Portion completed: " & Format(Iteration / CellCount, "##0.00%")
 If Cell.Value = "" And Not IsEmpty(Cell.Value) Then Cell.ClearContents

Application.Calculation = OriginalCalculationState
Application.StatusBar = ""

End Sub

Post Revisions:

This post has not been revised since publication.

Written by Jacob Walker

December 30th, 2015 at 9:31 am

Leave a Reply

%d bloggers like this: