Find and Replace Blank Cells with 0 is not working

[slam]

Hi,

I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20526) 32-bit. It's on Windows 10 Enterprise (64-bit) Version 21H2 OS build 19044.1110. i7-8565U & 16 GB RAM.

I have an activity I have to perform regularly that never works correctly. It is simply to replace a lot of blank cells (about 45,000) with a 0. I select the range, go into Find and Replace, leave Find What blank, put a 0 in Replace with, and check Match entire cell contents. Even though I've got 45,000 cells selected, when I click Replace All, maybe only 600 are replaced. If I click it again, maybe 4 are replaced. If I click it again, 1 is replaced. If I select a smaller amount of cells within the range, I can maybe get 10,000 replaced. It's a tedious and inconsistent process to go through until I can get all cells replaced. Does anyone know the reason this is happening, or if there's a workaround? It's a company laptop, so I don't have the freedom to change any specs.

Thanks

 

[Micron]

I looked but can't find any search results that suggest there is a cell limit, although there seems to be a character limit. If you can't just regard empty cells as zeros, then maybe this method would work for you

Add Zeros to Blank Rows in Excel - AbsentData

You can easily add zeros to blank rows in Excel using the Go-To-Special Window by pressing F5. You can highlight all the blank rows. You can follow the instruction below to understand the steps to add Zeros into your data How to Add Zeros to Blank Rows. You can how easily it is to fill […]

absentdata.com

Don't know what either method would do to cells with formulas that don't produce an actual result.

 

[Akuini]

Try turning off some functionality.
Before you do Find and Replace, run this code:

Sub TO_OFF()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual
End Sub

After Find and Replace has finished, run this code:

Sub TO_ON()
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub

 

[slam]

Hi both,

Thank you for the replies. It will be a month or two before I repeat this activity, but I have these tips saved to try out next time.

Thanks!

 

[Micron]

You might copy the workbook and revert all 0 to "" then try either suggestion.