blank cells being counted ??

  • Thread starter Thread starter Kelvin
  • Start date Start date
K

Kelvin

Wondering if someone could help with this problem
After doing a vlookup and then cleaning up with pastespecial/values
I am finding that the visually empty cells are still being counted in my
counta totals
I have tried this subroutine to clearcontents on the empty cells, which
works if I do it manually.

Sub clearempty()
For i = 3 To 588
If Cells(i, 14) = "" Then
ActiveCell.clearcontents
End If
Next i
End Sub

Can anyone tell me 1. why my clearcontents subrouting is not clearing
and 2. is there another way to clear out this invisible data in the blank
cells?

Thanks
Kelvin
 
You did NOT actually acivate the cell but you shouldn't. Try it this way to
remove the dreaded space bar. Notice the
" " instead of "". A way I use often to test is

if(len(application.trim(cells(i,14)))<1 then

Sub clearempty()
For i = 3 To 588
If Cells(i, 14) = " " Then cells(i,14).clearcontents
Next i
End Sub
 
Don,
Thanks for the info.
I had to change the " " to "" for the subroutine to initiate the then
statement.

Could you explain the if(len.... for me?

Thanks , I am now clearing the cells and they are counting correctly with
counta.
 
I would probably use this...

If Len(Replace(Cells(I, 14), " ", "")) = 0 Then Cells(I, 14).ClearContents

or even this...

If Replace(Cells(I, 14), " ", "") = "" Then Cells(I, 14).ClearContents

in place of this...

If Cells(I, 14) = " " Then Cells(I, 14).ClearContents

just in case the cell contents were multiple spaces (which is what I think
you were trying to imply with your application.trim If-Then statement, but
without the call back to the worksheet function).

Rick
 
Hi Kelvin,

The problem is most likely that the empty cell is not empty. If you check
it to see if it ="" that finds if the cell is empty. If the cell contains a
hidden character it will not be empty and so it will fail the ="" text, but
you won't be able to see it. This is exactly what happens when you use
Paste Special Values in some cases. Cells occupied with these type of
characters still have a length of 0, which is what the LEN(cell)=0 or
LEN(cell)<1 determines.

If can clear these without VBA -

1. Select one of the none-empty blank cells and copy it
2. Press Ctrl F, Ctrl V - this will paste the contents of the blank-like
cell in the Find what box.
3. Choose Find All, Close. - all the blank - like cells are selected. Not
all versions of Excel have a Find All command.
4. Press Delete.

Cheers,
Shane
 
Back
Top