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.
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.
![[CiteULike]](http://nandeshwar.info/wp-content/plugins/bookmarkify/citeulike.png)
![[del.icio.us]](http://nandeshwar.info/wp-content/plugins/bookmarkify/delicious.png)
![[Digg]](http://nandeshwar.info/wp-content/plugins/bookmarkify/digg.png)
![[Facebook]](http://nandeshwar.info/wp-content/plugins/bookmarkify/facebook.png)
![[Furl]](http://nandeshwar.info/wp-content/plugins/bookmarkify/furl.png)
![[Google]](http://nandeshwar.info/wp-content/plugins/bookmarkify/google.png)
![[Reddit]](http://nandeshwar.info/wp-content/plugins/bookmarkify/reddit.png)
![[Slashdot]](http://nandeshwar.info/wp-content/plugins/bookmarkify/slashdot.png)
![[StumbleUpon]](http://nandeshwar.info/wp-content/plugins/bookmarkify/stumbleupon.png)
![[Technorati]](http://nandeshwar.info/wp-content/plugins/bookmarkify/technorati.png)
![[Twitter]](http://nandeshwar.info/wp-content/plugins/bookmarkify/twitter.png)
![[Email]](http://nandeshwar.info/wp-content/plugins/bookmarkify/email.png)
Recent Comments