would like to delete rows with blank cell. but excel thinks it is not blank is blank but excel thinks it is not

[shammie4]

Enigma: there are cells in my workbook that are "blank" even when you hover the mouse over the cell the formula bar is blank. However when I print the page I get 8 blank pages. When I go to print preview the cells are blank but excel thinks that it is used so it is not blank.
when I use formula -=isblank(G9) it returns a FALSE VALUE. But it is actually empty.
I created this formula to delete blank cells in volums G and H but my macro is bombing.

Sub Macro1()
'
' Macro1 Macro
'
Dim wks As Worksheet
For Each wks In Worksheets
Columns("G:H").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Next wks
End Sub

It works on a few sheets and then ingnore other sheets in the workbook.
Can someone point me in the right direction?
Thanks

 

[Datsmart]

If =isblank(G9) returns FALSE, then the cell is not Blank, Selection.SpecialCells(xlCellTypeBlanks).Select will not Select it.

ASCII characters sometimes come over from mainframe extracts. TRIM and CLEAN do not pick up ASCII characters 127 and 160. The procedure below will remove them. It will also remove leading and trailing spaces.

Sub CleanSelection()
Dim c As Range, Rng As Range
Set Rng = Intersect(Selection, Selection.Parent.UsedRange)
    If Rng Is Nothing Then
        MsgBox "No cells with values!"
        Exit Sub
    End If
    For Each c In Rng
        If Not IsError(c) Then
            c.Value = MEGACLEAN(c)
        End If
    Next c
End Sub

Function MEGACLEAN(varVal As Variant)
Dim NewVal As Variant
If IsMissing(varVal) Then Exit Function
NewVal = Trim(varVal) 'remove spaces
NewVal = Application.WorksheetFunction.Clean(NewVal) 'remove most unwanted characters
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(127), "") 'remove ASCII#127
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(160), "") 'remove ASCII#160
MEGACLEAN = NewVal
End Function

Be sure to input both Codes into a VBA Module.
Select the range you want to clean, then run the first code.

Here is where I found the code:
http://www.mrexcel.com/forum/showthread.php?t=197984

 

[Datsmart]

Sorry, Double post...

 

[shammie4]

Hi,
Sorry for the delayed response. I tried the macro but it did not work. It seems as if excel still thinks that the cell is non blank even though the cell appears empty on screen as well as in the formula bar.

 

[VoG]

Say A1 appears blank but isn't. What does this formula return

=CODE(A1)

 

[Rick Rothstein]

Sub[/code]

Function MEGACLEAN(varVal As Variant)
Dim NewVal As Variant
If IsMissing(varVal) Then Exit Function
NewVal = Trim(varVal) 'remove spaces
NewVal = Application.WorksheetFunction.Clean(NewVal) 'remove most unwanted characters
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(127), "") 'remove ASCII#127
NewVal = Application.WorksheetFunction.Substitute(NewVal, Chr(160), "") 'remove ASCII#160
MEGACLEAN = NewVal
End Function

There is no need to calll out to the worksheet's SUBSTITUTE function when VB has its own built-in Replace function which does the same thing. Here is how I probably would have written the MEGACLEAN function...

Function MEGACLEAN(S As String) As String
  If Len(S) Then
    MEGACLEAN = Trim(S) 'remove leading/trailing spaces
    MEGACLEAN = WorksheetFunction.Clean(MEGACLEAN) 'remove most unwanted characters
    MEGACLEAN = Replace(MEGACLEAN, Chr(127), "") 'remove ASCII#127
    MEGACLEAN = Replace(MEGACLEAN, Chr(160), "") 'remove ASCII#160
  End If
End Function

Although in thinking about it, I might have condensed it down to one line of code instead...

Function MEGACLEAN(S As String) As String
  If Len(S) Then MEGACLEAN = Replace(Replace(WorksheetFunction.Clean(Trim(S)), Chr(127), ""), Chr(160), "")
End Function