Macro to delete hidden values in empty cells

[TooZippy]

I downloaded a spreadsheet from another software program. In that spreadsheet I have a formula that references a cell to the left of the cell with the formula. The formula does not calculate properly because there is a hidden value in the cell being referenced. When I press the delete button on the cell with the hidden value, the formula works properly.

I don't want to delete the values from all of the cells in the column but only the ones that look empty but have a hidden value in them. I want to keep the cells in tact that have values that I could see. I tried using this macro that I found on the internet but I get the debug screen when I try to run the macro. It highlights "If usedrng.Value = "" Then". I take it that the VBA is telling me that is where the error is. I don't know how to wright code yet and I need some help. If there is someone that could help me, I would appreciate it.

Thank you,
​

[COLOR=#26282A][FONT="Helvetica"]Sub clear_empty_cell()[/FONT][/COLOR]
[FONT=Calibri][SIZE=3][COLOR=#000000]
Dim usedrng As Variant

Worksheets("ABC").Range("E1:E20").Select
    For Each usedrng InActiveSheet.UsedRange
        If usedrng.Value ="" Then
             usedrng.ClearContents
        End If
    Next
End Sub[/COLOR][/SIZE][/FONT]

​

 

[Tom Urtis]

The way to attack this is to find out what those invisible values are in cells that look blank but are not truly empty. Suppose that one such cell is A3. In some other cell, enter the formula =CODE(A3) and see what number is returned from that. It will probably be the number 2, 9, 10, 13, or 28-32.

If that returned number is 2 or 9, then select any single cell or all cells, Ctrl+H and in the Find What field press Alt+0002 and leave the Replace With field empty, and hit Replace All. If the returned number is 9, 10, 13, or 28-32, then in the Replace What field you press Alt+00## where ## is the two digits returned by the formula.

You should be good to go after that.

 

[TooZippy]

The way to attack this is to find out what those invisible values are in cells that look blank but are not truly empty. Suppose that one such cell is A3. In some other cell, enter the formula =CODE(A3) and see what number is returned from that. It will probably be the number 2, 9, 10, 13, or 28-32.

If that returned number is 2 or 9, then select any single cell or all cells, Ctrl+H and in the Find What field press Alt+0002 and leave the Replace With field empty, and hit Replace All. If the returned number is 9, 10, 13, or 28-32, then in the Replace What field you press Alt+00## where ## is the two digits returned by the formula.

You should be good to go after that.

Mr. Urtis,

Thank you for your help! I will give it a try on Monday. I will let you know if it works or not. Thanks again.

Jared Z.

 

[TooZippy]

Mr Urtis,

I used =Code(E1) for all of the cells that have a hidden value and I got a #VALUE error message for the cells I want to delete the hidden values from. I tried this macro instead. It ran without activating the debugger notification but it did not delete the contents of the cell's that had the hidden values. Can you help me with this macro please?

[LEFT][COLOR=#26282A][FONT=Helvetica Neue]Sub clear_empty_cell()[/FONT][/COLOR]

[COLOR=#26282A][FONT=Helvetica Neue]   Dim strLast As String[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   Dim rng As Range[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   Dim cel As Range[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   Dim var As Variant[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   [/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   strLast = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   Set rng = Worksheets("ABC").Range("E1:E100")[/FONT][/COLOR]

[COLOR=#26282A][FONT=Helvetica Neue]   For Each cel In rng.Cells[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]     var = cel.Value[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]     If IsNull(var) Then[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]        cel.ClearContents[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]     End If[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   Next[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]   MsgBox "Done"[/FONT][/COLOR]
[COLOR=#26282A][FONT=Helvetica Neue]End Sub
[/FONT][/COLOR][/LEFT]

Thank you,

Jared Z

 

[Michael M]

Maybe this from David McRitchie

Sub TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
   Application.ScreenUpdating = False
   Dim cell As range
   'Also Treat CHR 0160, as a space (CHR 032)
   ActiveSheet.UsedRange.Replace What:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.ScreenUpdating = True
End Sub

 

[TooZippy]

Maybe this from David McRitchie

Sub TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
   Application.ScreenUpdating = False
   Dim cell As range
   'Also Treat CHR 0160, as a space (CHR 032)
   ActiveSheet.UsedRange.Replace What:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.ScreenUpdating = True
End Sub

Thank you Michael! I will try it out tomorrow and let you know if it works or not when I get home.

Jared Z

 

[TooZippy]

Michael,

I ran the Macro today and it deleted the contents of the cell with the null value. However, it did not delete the contents of other cells in the same column that also had a null value but showed empty. It only deleted the contents of cells that I selected and highlighted. I want to delete the contents of all cells in a column that have a null value. Any suggestions please?

Thank you,

Jared Z.