fill blank cells with 0

[lezawang]

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.

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

 

[MARK858]

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?

 

[Rick Rothstein]

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.

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.

 

[Fluff]

That is going to put a 0 in EVERY blank cell in col A all the way down to row 1048576

 

[Rick Rothstein]

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...

Sub PutZeroInEveryBlankCellInColumnA()
  [A:A] = [IF(A:A="",0,A:A)]
End Sub

 

[lezawang]

Thank you very much all.

 

[Fluff]

You're welcome