CODEWITHSUNDEEP
excelvba
Nick
Nick

Reputation: 162

How do I clear content in rows based on cell value?

I copy a lot of information (1000 rows and at least 24 columns) from one sheet to another. A lot of the cells contains "". This makes my other formulas(for example: A1-B1) to show an value error if either of these cells contains "". I believe I can solve the problem by never pasting "" but a "0" instead. But I would like to delete these "0" afterwards.

There could be values in the first 3 rows but the other 997 rows have "".

I would think I need to tell my macro to (Cell A1 in the "sheet1" sheet displays "G5:H12". the cells I need to delete): 

Rowstodelete = Sheets("sheet1").Range("A1").Value

Sheets("sheet1").Range("rowstodelete").clearcontent

This does not work. anyone know how to do this?

Summary(new example)

If cell A1 = "B1:B2" I want to clear the content of B1 and B2, but if A1 now = B4:B6, that is the cells that should be cleared.

Upvotes: 0

Views: 186

Answers (1)

David García Bodego
David García Bodego

Reputation: 1090

Try this one:

With Worksheets(1).Range( _'PLACE YOUR RANGE
                                            ) 
    Set c = .Find(0, lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = c.Address 
        Do 
           c.Value = "" 
            Set c = .FindNext(c) 
        Loop While Not c Is Nothing
    End If 
End With

Hope it helps

Anyhow, I think that will be simpler to place a condition in your operation formula: =IF(OR(A1="",B1=""),Make when there is an unexpected value,A1-B1)

Upvotes: 1

Related Questions