Delete contents from cell, IFIsNumeric = False

Question

So I have this program; that scans values from a remote system. At times it can timeout if network is down and it will put in #nan error or something similar.

When I try and write that error message to a database; it errors out for a "Data Type Mismatch" since it is looking for a numerical value.

To get around this I decided to try and clean up the results. So I have that code below. It works most of the time; what I am seeing it fail at is if there is a numerical number halfway through and then it errors again. Once it gets to the numerical number it seems like it stops the loop and does not clean anything else.

Any help someone can give me would be great; and maybe I am doing this a bad way. I tried to give examples of what I am trying to see below. Please let me know if you have any suggestions or questions.

Private Sub Clean()
'Select Current Row For where to start cleaning. LoopIndex is a global variable. 
'Just giving the line of where to read from so I do not need to read the whole sheet. 
  Range("B" & LoopIndex).Select

'Start the loop, to go through the entire row.
  Do Until IsEmpty(ActiveCell)

   'Checks if it is a number value; if it is not it will clear out the data.
    If IsNumeric(ActiveCell.Value) = False Then
        ActiveCell.Value = ""
    End If
  ActiveCell.Offset(0, 1).Select
 Loop
End Sub

What it looks like.

|A     |B     |C     |D     |
|#error|#Error|3     |#Error|

What I want it to look like.

|A     |B     |C     |D     |
|      |      |3     |      |

What it is doing.

|A     |B     |C     |D     |
|      |      |3     |#Error|

Where'd you get `LoopIndex`? Also, it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — BruceWayne, Mar 14 '17 at 16:12
It is a global variable that I assigned to it. I had that in the comment section. — user2644176, Mar 14 '17 at 16:25
@user2644176 I'd be careful using do until loops. IMO it's much safer to specify the range you want to clean and simply loop through the cells to clean them. Is your range variable? Is there a reason my answer won't work for you? Let me know and I will help you get it working. As for why yours isn't, I would try something besides `IsEmpty` - May want to use `ActiveCell = ""` instead. Also, it seemed to work okay on my machine (though for just one line). — user1274820, Mar 14 '17 at 16:30

user1274820 · Accepted Answer · 2017-03-15T19:36:24.700

4

Try this

Sub RemoveErrors(TheRange As Range)
Dim c
For Each c In TheRange
    If IsError(c) Then c.Value = ""
Next c
End Sub

Edit: Version without loop that may speed things up a bit if they are formula errors

Sub RemoveErrors(TheRange As Range)
On Error Resume Next
TheRange.SpecialCells(xlCellTypeFormulas, xlErrors).Value = vbNullString
On Error GoTo 0
End Sub

Use it like this:

Sub Test()
RemoveErrors Range("A1:D21")
End Sub

If you prefer to use the IsNumeric function, then use this:

Sub RemoveNAN(TheRange As Range)
Dim c
For Each c In TheRange
    If IsNumeric(c) = False Then c.Value = ""
Next c
End Sub

edited Mar 15 '17 at 19:36

answered Mar 14 '17 at 16:15

user1274820

7,786
3
37
74

1

Thank you. This appears to work well. The range will be variable that that is no issues. I like the For Each and IsError that you did here. Seems much more elegant that how I was doing it. I appreciate it. – user2644176 Mar 14 '17 at 16:35

score 1 · Answer 2 · answered Mar 14 '17 at 17:51

This is how I ended up doing it, for the variables ranges on different sheets. I am only posting this to give others a solution in the future.

Thank you for the help everyone on this.

 Private Sub Clean()

Dim StartRow As String
Dim LastCol As Long
Dim EndRow As String
Dim StartCol As String
Dim MyCol As String

StartCol = "B"


With ActiveSheet
        LastCol = .Cells(LoopIndex, .Columns.Count).End(xlToLeft).Column
End With

MyCol = GetColumnLetter(LastCol)


RemoveErrors Range(StartCol & LoopIndex & ":" & MyCol & LoopIndex)


End Sub

'Does the replacing
Sub RemoveErrors(TheRange As Range)
Dim c
For Each c In TheRange
    If IsError(c) Then c.Value = ""
Next c
End Sub

'Gets the Column Letter
Function GetColumnLetter(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)
End Function

Delete contents from cell, IFIsNumeric = False

2 Answers2