Solved: cell is not emtpy even if no visible value

[danlu]

Hi,

I have a code where I want to clear cells in a column based on if there is a value in the cell to the left of the cell that is evaluated wether it can be cleared or not.

Sub ngt()
Dim c As Range

For Each c In Worksheets("Sheet1").Range("M1:M100").Cells
If c = "" Then c.Offset(0, 1).Clear
Next
End Sub

Though when I try to run this code it won't clear the cells in column N eventhough the criteria is fullfilled. But if a go to a cell that seems to be empty in column M and press delete button and then run the code again, then my code realize that the cell is empty and proceed with deleting the value in on the same row in column N.
How Can my code be modified to detect that there is no visible value in column M (eventhough there might be something else as today when the code doesn't realize that the cell is empty)?

Or can I run some other code before the one above which identifies the cells that have no visible value and actually make these really empty so afterwards when running the code above, it will understand which cells are empty in column M.

[MikeO]

Yeah, that's a common irritating problem. Maybe this will work for you:

[VBA]Sub ngt()
Dim c As Range
For Each c In Worksheets("Sheet1").Range("M1:M100").Cells
If Trim(c.Value) = "" Then c.Offset(0, 1).Clear
Next

End Sub
[/VBA]

[danlu]

Hi,

It worked, thanks a lot! This will really facilitate my work!