Clearing Contents of "Empty-Appearing" Cells (VBA)

[NBVC]

Hi again,

I have a macro to delete rows based on a selection of entire rows...it works fine.....

except, for some reason the data we get has some (possibly not printable white space in cells)...because there doesn't seem to be anything in them (i.e. Len(a1)=0)....yet they won't allow the row to delete. If I do an Edit|Go To and look for Constants, those "mysterious" cells get included in the selection....

I came up with this simple way to take care of it, but it takes forever since the user selects entire rows and not a specific range.

Is there a faster way to do this loop and clear those "empty-appearing" cells?

Dim c as Range

Set c = Selection

For Each c In Selection
     If Len(c) = 0 Then c.ClearContents
Next c

Thanks..

 

[Richard Schollar]

Vittorio

Try this alternative code to remove any non-printing characters:

Sub test()
Selection.Value = Evaluate("IF(ROW(" & Selection.Address & "),CLEAN(" & Selection.Address & "))")
End Sub

Note this won't really be an option if you have genuine non-printing characeters (like Carriage Returns) in the selection that you want to keep.

 

[VoG]

Try without the Set statement - I'm not sure that you need it

Dim c as Range


For Each c In Selection
     If Len(c) = 0 Then c.ClearContents
Next c

 

[NBVC]

Hi guys,

Thanks very much

Both suggestions do work....but they don't speed up the process much.

I will try any other suggestions you may have for me tomorrow as I am off now.

Looking forward to anything else you might have..

 

[Richard Schollar]

If you don't have any non-printing characters in your entire worksheet range, then it may speed things ups to simply do the clean on the whole sheet first - something along the lines of:

With ActiveSheet
   .UsedRange.Value = Evaluate("IF(ROW(" & .UsedRange.Address & "),CLEAN(" & .UsedRange.Address & "))")
End With

 

[NBVC]

If you don't have any non-printing characters in your entire worksheet range, then it may speed things ups to simply do the clean on the whole sheet first - something along the lines of:

With ActiveSheet
   .UsedRange.Value = Evaluate("IF(ROW(" & .UsedRange.Address & "),CLEAN(" & .UsedRange.Address & "))")
End With

Thanks very much, Richard....

That worked very well and very fast. I appreciate your help once again.

And just so nobody thinks I was waiting to be spoonfed, I did think of and try another way (which proved fast, but not quite as fast as yours)..

I thought if I program the selection of constants in the range and loop only through those, then, it would be faster than yesterday's macro (which looped through every cell in the selected rows).... I was right....but, again, because there still is some looping involved, it still is slower than selecting a range and clearing all at once...as you have shown.

This is the code I had devised, but I think I'll will change to your suggestion.

Sub DeleteBlankRows()


' Deletes the entire row within the selection if the ENTIRE row contains no data.
' Revised March 14, 2007 - added For/Next loop to ensure "empty appearing" cells are cleared.

On Error Resume Next

Dim i As Long
Dim c As Range, OrigRange As Range

    
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
        
        
    ' Set original selection for later use
    Set OrigRange = Selection
    
' clear contents of seemingly empty cells that are really not empty
    Selection.SpecialCells(xlCellTypeConstants, 23).Select

    For Each c In Selection
         If Len(c) = 0 Then c.ClearContents
    Next c
      
    'Delete blank rows starting at bottom of selection
    For i = OrigRange.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(OrigRange.Rows(i)) = 0 Then
            OrigRange.Rows(i).EntireRow.Delete
        End If
    Next i
    
    Range("A1").Select

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

Edit: One thing I did note about yours, Richard, is that yours will convert all numbers stored as text to general.... the user I am designing this for, I think, requires that they stay as text....not sure why, but I think so.

 

[Richard Schollar]

Edit: One thing I did note about yours, Richard, is that yours will convert all numbers stored as text to general.... the user I am designing this for, I think, requires that they stay as text....not sure why, but I think so.

Vittorio

That certainly does seem to tbe the case (which does surprise me I must add) - you could possibly set the number format of the usedrange to text prior to doing the Evaluate bit, which should maintain the text format after the Clean (true numbers will be left-justified following this, but will still function as numbers - at least they have in my limited testing!):

With ActiveSheet.UsedRange
   .NumberFormat = "@"
   .Value = Evaluate("IF(ROW(" & .Address & "),CLEAN(" & .Address & "))") 
End With

 

[NBVC]

Edit: One thing I did note about yours, Richard, is that yours will convert all numbers stored as text to general.... the user I am designing this for, I think, requires that they stay as text....not sure why, but I think so.

Vittorio

That certainly does seem to tbe the case (which does surprise me I must add) - you could possibly set the number format of the usedrange to text prior to doing the Evaluate bit, which should maintain the text format after the Clean (true numbers will be left-justified following this, but will still function as numbers - at least they have in my limited testing!):

With ActiveSheet.UsedRange
   .NumberFormat = "@"
   .Value = Evaluate("IF(ROW(" & .Address & "),CLEAN(" & .Address & "))") 
End With

Richard,

This one doesn't seem to work for me. It seems to have converted all cells in the used range to "contain" constants. So If I select the range (after running the macro) and go to Edit|Go To|Special Cells|Constants.....everything is highlighed.

Any idea what it could be?

 

[Richard Schollar]

Sorry Vittorio - it will stuff up any formulas because it is effectively a values assignment

I need to have a think about this...