CODEWITHSUNDEEP
vb.netexcel
Shery
Shery

Reputation: 1882

Clear contents in Cells based on String/empty cell

I am trying to clear cell contents if I find "na" as a cell value or if the cell is already blank.

Example looks like this:

Rep Intakes Var Plan CAL Rep Intakes    Act CAL Rep Intakes
na                  373.00                      na
8.00                371.00                     374.00
23.00               379.00                     358.00
69.00               398.00                  
-18.00              175.00                     148.00
11.00                 na                       252.00

My numeric column start from E to ARA I want to write VB code which looks at given column range and checks each cell to check whether it is empty or "na". In both cases it should clear the cell.

Upvotes: 0

Views: 1324

Answers (2)

Shery
Shery

Reputation: 1882

I wrote two Methods to sort this out:

Sub Clear_Empty_Cells_Contents()

Dim cell As Range

With ActiveSheet
    For Each cell In .Range("E2:ARA350")
        If IsEmpty(cell) Then
            cell.ClearContents
        End If
    Next
End With

End Sub

Sub Clear_Cells_based_on_value()

Dim cell As Range

With ActiveSheet
    For Each cell In .Range("E2:ARA360")
        If cell.Value = "na" Then
            cell.ClearContents
        End If
    Next
End With

End Sub

UsedRange is a good way and I will recommend that (I didn't know this before)

The problem I had was:

  1. I had na in numeric type columns and it was ruining everything when I was taking this data in R.
  2. Excel was putting "" in blank cells and R was treating it as a value.

From Above methods i was able to remove "" and na.

Thanks both for your help.

Regards

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Try this:

Sub naKiller()
    Dim r As Range, v As String
    For Each r In ActiveSheet.UsedRange
        v = r.Text
        If v = "na" Or v = "" Then
            r.Clear
        End If
    Next r
End Sub

Upvotes: 1

Related Questions