Saturday, April 23, 2005
True blanks
This about the problem of cells which appear to be blank and which are true blanks. If you check a cell using IsBlank() formula, you'd know if it's a true blank or not.
Even if you copy paste special (values) this ="", there wouldn't be anything in the cell and length of that cell would be zero - still it won't be a blank. But if you press delete on that cell it would become a true blank.
Now, here is a macro to do the same thing, it would check if the cell has zero length and check if Istext is true, if both conditions are satisfied it would press a delete key on that cell.
Please check the Excel help on the functions- Len, Istext, and Isblank
To use this macro Press Alt + F11, double click the sheet you're working on, copy and paste this code. Close VBA window.
To run it, select a range in the worksheet you want true blanks, press Alt + F8, select this macro and press Run.
Please check this discussion on mrexcel.com.
Even if you copy paste special (values) this ="", there wouldn't be anything in the cell and length of that cell would be zero - still it won't be a blank. But if you press delete on that cell it would become a true blank.
Now, here is a macro to do the same thing, it would check if the cell has zero length and check if Istext is true, if both conditions are satisfied it would press a delete key on that cell.
Please check the Excel help on the functions- Len, Istext, and Isblank
To use this macro Press Alt + F11, double click the sheet you're working on, copy and paste this code. Close VBA window.
To run it, select a range in the worksheet you want true blanks, press Alt + F8, select this macro and press Run.
'macro to make a blank true blank
Sub make_true_blanks()
Dim c As Range
For Each c In Selection
If Len(c) = 0 And WorksheetFunction.IsText(c) Then
c.ClearContents
End If
Next
MsgBox "Done"
End Sub
Sub make_true_blanks()
Dim c As Range
For Each c In Selection
If Len(c) = 0 And WorksheetFunction.IsText(c) Then
c.ClearContents
End If
Next
MsgBox "Done"
End Sub
Please check this discussion on mrexcel.com.
