Macro to delete hidden values in empty cells

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
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,
Code:
[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]


 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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.
 
Last edited:

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
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

Board Regular
Joined
Dec 30, 2018
Messages
70
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?
Code:
[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

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Maybe this from David McRitchie

Code:
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

Board Regular
Joined
Dec 30, 2018
Messages
70
Maybe this from David McRitchie

Code:
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

Board Regular
Joined
Dec 30, 2018
Messages
70
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top