Simple Macro - If Cell Value Is "X" then clear another cell

**donjman** · 11-11-2011, 10:02 AM

Hey guys,

I'm rubbish at macros, I'd really appreciate someones help with a simple quick macro. I've got column B with values in, and column D with values in.

I'm trying to make a macro that clears the cell in column D, based on the column B cell being value X. and it would do this for all cells in the whole of both columns,

Can anyone help? I'd really appreciate it, I've been stuck for hours

**JonathanMoore** · 11-11-2011, 10:17 AM

Hi,

Try this:

Dim x As Integer
          
           
    For x = 2 To 45  ' Change "45" to the last row in your document.
        If Range("B" & x).Value = "x" Then
        Range("D" & x).Select
        ActiveCell.ClearContents
    End If
    Next x

This code will work if you assign it to a command button or something like that,

Cheers,

- Jon

**davesexcel** · 11-11-2011, 10:24 AM

Try this

Sub delete()

    Dim Rws As Long, Rng As Range, Inp As Integer, c As Range

    Inp = InputBox("Enter Value to Find", "Hello")
    Rws = Cells(Rows.Count, "D").End(xlUp).Row

    Set Rng = Range(Cells(1, 4), Cells(Rws, 4))

    For Each c In Rng.Cells
        If c.Offset(0, -2) = Inp Then c = ""
    Next c


End Sub

**Domski** · 11-11-2011, 10:31 AM

An approach using the Find method:

Sub Test()
    Dim rngFindRange As Range
    Dim strFirstAdd As String
    Dim rngFoundRange As Range
    With Columns("B")
        Set rngFindRange = .Find("X", LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFindRange Is Nothing Then
            strFirstAdd = rngFindRange.Address
            Set rngFoundRange = rngFindRange
            Do
                Set rngFoundRange = Union(rngFindRange, rngFoundRange)
                Set rngFindRange = .FindNext(rngFindRange)
            Loop While Not rngFindRange Is Nothing And rngFindRange.Address <> strFirstAdd
        End If
    End With
    If Not rngFoundRange Is Nothing Then
        rngFoundRange.Offset(0, 2).ClearContents
    End If
End Sub

Dom