fill blank cells with 0

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I want to fill blank cells with 0. The code below gave me error message
complaining that rows.count overflow. I changed it to 1000 and it worked
My question, why I got that error message, and how you would do it
using your programming style? Thank you very much.

Code:
Sub fillblankcells()
    Dim i As Integer
    For i = 1 To Rows.Count
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = 0
        End If
    Next i
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Change Dim i As Integer to Dim i As Long but do you really want to fill every cell in column A with a zero... why?
 
Last edited:
Upvote 0
Hi
I want to fill blank cells with 0. The code below gave me error message
complaining that rows.count overflow. I changed it to 1000 and it worked
My question, why I got that error message, and how you would do it
using your programming style? Thank you very much.

Code:
Sub fillblankcells()
    Dim i As Integer
    For i = 1 To Rows.Count
        If Cells(i, 1).Value = "" Then
            Cells(i, 1).Value = 0
        End If
    Next i
End Sub
You Dim'med the 'i' variable as Integer... the maximum number an Integer variable can hold is 32767... there are well over that many rows on a worksheet, so the variable over-flowed. You should Dim that variable as Long (maximum possible value of 2147483647). As a matter of fact, you should consider always using Long for variables that will hold whole numbers.
 
Upvote 0
That is going to put a 0 in EVERY blank cell in col A all the way down to row 1048576
 
Upvote 0
That is going to put a 0 in EVERY blank cell in col A all the way down to row 1048576
Which, if that is what the OP truly wants to do and if Column A has no formulas in it, then this one-liner will do the job...
Code:
Sub PutZeroInEveryBlankCellInColumnA()
  [A:A] = [IF(A:A="",0,A:A)]
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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