Clear cells with specific contents in VBA

Question

I have the data output of an excel model. The model spits out all of the data you would want, but to accomplish this task, it also puts a "space" character in every cell that would otherwise be blank. - and I mean every cell, out to XFD1048576.

I need to take ranges of that data and transpose them into database format, which I'm confident I could do if I could clear the contents of any cell with a "space" in it so that - Range(Selection, Selection.End(xlDown)).Select - will just select data.

Is there an easy way to have VBA select the entire sheet, and clear the contents of any cell with specific content?

Siddharth Rout- Thanks! works like a charm. Looks like I need to beef up my knowledge of VBA functions. — Dunce Cap Aficionado
Just realized I can't use it on every cell without giving more conditions, some of cells have data that have spaces in them between words that I need to preserve. Can this be done with an If-Then-Else in conjunction with .replace, perhaps? — Dunce Cap Aficionado
@DunceCapAficionado why don't you try implementing the logic (if/then/else/etc.) for yourself, and see what happens? — David Zemens
@DavidZemens You are right, I'm being lazy, I'm not sure of the syntax for operating them together but that's a good learning experience. Thanks David. — Dunce Cap Aficionado

Steve Gon Steve Gon · Accepted Answer · 2013-12-22T07:10:18

Public Function Get_Last_Row_Find(ByVal rngToCheck As Range) As Long
    Dim rngLast As Range
    Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows, SearchDirection:=xlPrevious)
    If rngLast Is Nothing Then
        Get_Last_Row_Find = rngToCheck.Row
    Else
        Get_Last_Row_Find = rngLast.Row
    End If
    If Get_Last_Row_Find <= 1 Then
        Get_Last_Row_Find = 2
    End If
End Function
Public Function Get_Last_Col_Find(ByVal rngToCheck As Range) As Long
    Dim rngLast As Range
    Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByColumns, SearchDirection:=xlPrevious)
    If rngLast Is Nothing Then
        Get_Last_Col_Find= rngToCheck.Column
    Else
        Get_Last_Col_Find = rngLast.Column
    End If
    If Get_Last_Col_Find<= 1 Then
        Get_Last_Col_Find= 2
    End If
End Function
Sub Main
    x = Get_Last_Col_Find(Activesheet.Range("A1:XFD1")
    y = Get_Last_Row_Find(Activesheet.Range("A1:A999999")
    for i = 1 to x
        for j = 1 to y
            if Activesheet.Range("A1").Offset(i-1,j-1).Value = chr(32) then
                 Activesheet.Range("A1").Offset(i-1,j-1).Value = ""
            endif
        next j
    next i

end sub

You can also try this Add In: http://www.microsoft.com/en-us/download/details.aspx?id=21649

Clear cells with specific contents in VBA

1 Answers