Activecell.text on a Hidden Cell

[SeaDub]

Got a bit of a strange one.

Looping through a group of cells, and if the cell value is blank, I want to put a value in the cell (in this case, 0).

Heres the problem. When the cells are visible, it works fine. When the cells are hidden, the activecell.text is equal to "". If I run the code on the same cells when not hidden, the text is equal to the cell value (normally a number).

Sub RemoveBlanks()
    Range("A1").Select
    While ActiveCell.Row <= Range("C10").Row
        While ActiveCell.Column <= Range("C10").Column
            If ActiveCell.Text = "" Then
                    ActiveCell.Value = 0
            End If
            ActiveCell.Offset(0, 1).Activate
            colCounter = colCounter + 1
        Wend
        ActiveCell.Offset(0, 0 - colCounter).Activate
        colCounter = 0
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

Also, I'm aware I could use a Goto Special blanks on this example, but in my application the range is a lot more complicated and I would not be able to do this.

Any thoughts on why excel cannot see hidden cells .text?

Any input would be greatly appreciated! Thanks so much.

 

[mikerickson]

Because the text to show is "" if the cell is hidden.

Perhaps if you replaced myCell.Text with Format(myCell.Value, myCell.NumberFormat).

 

[RoryA]

Do you actually need to use .Text instead of .Value for some reason?
(curiously, .Text seems to return the formatted value if the row is hidden, but "" if the column is hidden)

 

[SeaDub]

Mike - Your solution of changing:

If ActiveCell.Text = "" Then

to

If Format(ActiveCell.Value, ActiveCell.NumberFormat) = "" Then

seems to work. Thanks so much for your help.

rorya - I was using .text instead of .value as I was getting errors on my calcs as sometimes my cell values are Strings, Numbers, even #VALUE. Who doesn't love a good #VALUE.

Thanks to both of you for your unbelievably fast help.

 

[mikerickson]

If you are just trying to clean up your data types.

CStr(ActiveCell.Value) will always return a string, protecting against error values (e.g. #DIV/0)

Val(CStr(ActiveCell.Value)) will return a number, (non-numeral) strings and errors are treated as 0.

 

[RoryA]

If you have errors in your cells, then I think Mike's code will also give you an error. You are better off checking that separately first:

If IsError(Activecell.Value) then
  ' do whatever you want for errors
ElseIf Len(ActiveCell.value) = 0 Then
                    ActiveCell.Value = 0
End If