Macro to clear contents based on a cell value

[rjacmuto32]

searched but wasn't able to find anything similar to this. Not sure If maybe my subject might have been wrong. Anyway I have data in rows.

I want a macro to go through each row and if column B for that row is blank, then clear the contents in columns C-L

So if B1 has a number nothing, If B2 is blank then clear the contents in cells C2-L2.

The other problem is I can have a differnt last row number each time. 1 day there is 500 rows another day there is 700 rows. There always is a value in Column A which can be used to locate the last row function. For that I use LastRow = Range("A" & Rows.Count).End(xlUp).Row I'm just not sure how to clear the contents is column B is blank of other cells.

Thanks,
--Robert

 

[njimack]

You mean like this?

Sub Clearcontents()
Dim LastRow as Long, n as Long

Application.ScreenUpdating = False

LastRow = Range("A" & Rows.Count).End(xlUp).Row 

For n = 1 to LastRow

If Cells(n,2)="" Then Range(Cells(n,3),(Cells(n,12).ClearContents
Next n

Application.ScreenUpdating = True

End Sub

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    Dim r As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For r = 1 To LastRow
        If Range("B" & r).Value = "" Then
            Range("C" & r & ":L" & r).ClearContents
        End If
    Next r
End Sub

 

[Lewiy]

Try this macro:

Sub Macro1()
Dim Limit As Long
Dim c As Long
Limit = Cells(Rows.Count, 1).End(xlUp).Row
For c = Limit To 1 Step -1
    If Cells(c, 2) = "" Then
        Range("C" & c & ":L" & c).ClearContents
    End If
Next c
End Sub

 

[Boller]

Dim rng As Range, area As Range
On Error Resume Next
Set rng = Range([B2], [A65536].End(xlUp)(1, 2)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each area In rng.Areas
    area.Resize(, 11).ClearContents
Next

 

[rjacmuto32]

Thanks. Those worked. I see some different parts of the code that I don't currently understand. I have something to research now can learn about some more things from this.

--Robert