VBA code to clear contents of all cells that look blank in a range

[parkerbelt]

I'm trying to find the vba code to "clear contents" - the equivalent of pressing the delete button, for all of the cells that look like they are blank in the range ("H6:0" & lastrow).

I have a cell that has something in it, but it doesn't look like it.
If I delete the contents of the cell, my macro works correctly.

 

[Peter_SSs]

Again are you sure it is one of the problem cells?
That is, if you select that cell and hit Backspace, does your other formula come good?

 

[TomDrury89]

Again are you sure it is one of the problem cells?
That is, if you select that cell and hit Backspace, does your other formula come good?

If I hit backspace on the problem cell my formula works, however Code(cell) still produces a #value. Its got me stumped

 

[Peter_SSs]

If I hit backspace on the problem cell my formula works,

Thanks for the confirmation. Possibly you have cells that contain a formula returning "" or the cells previously had a formula returning "" and has subsequently been copied and paste special - values or the export produces a similar result.

What happens if you select one of the columns of data including at least one of these problem cells and then do ...
Data -> Text to Columns -> Finish
.. and repeat for the other 3 columns?

By the way, what is your other formula that is being messed up by these cells?

 

[TomDrury89]

Thank your for helping with this Peter.

I think it must be something with the extract.

For further context I am dealing with time extracted time data looking for exceptions. The formula reads = ((B-C-D)-A)*24 .... with the expected result being 0. The * 24 is to get it to decimals. Unfortunaly i cannot do any calculations without filtering to blank and clearing the blank cells from column B, C, and D. I was hoping there was a macro i can run over all columns to essential clear the data.

A	B	C	D	Expected Result
35:00	35:00			0​
0:00				0​
4:45	4:15			-0.5​
38:00	39:42		1:42	0​
38:00	38:00			0​
38:00	42:15		4:15	0​
35:00				-35​
35:00	35:00			0​
38:00	42:15		4:15	0​
38:00	39:42		1:42	0​
24:00	32:00	8:00		0​

 

[Peter_SSs]

What was the result of this?

What happens if you select one of the columns of data including at least one of these problem cells and then do ...
Data -> Text to Columns -> Finish
.. and repeat for the other 3 columns?

 

[TomDrury89]

What was the result of this?

Hi Peter,

I hope you had a good weekend.

If i use text to column it fixes the problem cells that then allows the formula to work. Does this result give an indication on what could be hidden in the cells to have them not being originally read as blank? I am hoping to automate as much as possible.

Thanks.

 

[Peter_SSs]

I am hoping to automate as much as possible.

Assuming that the 4 columns of values are A:D, with the actual data starting in row 2, try this macro with a copy of your workbook.

Sub TTC()
  Dim i As Long
  
  For i = 1 To 4
    Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False
  Next i
End Sub