clearing a cell based on dropdown value

[dozibloke]

I have been trying to code a solution but can only get it to work on one row..

column A has identical cells which are a dropdown with value 1 and 2.
column b1, c1 and d1 have text entry cells which a user can put values in if A1=1
column e1, d1 and e1 have text entry cells which a user can put values in if A1=2

However if a user choses A1=1 and puts values in b1, c1 and d1, if they then change the A1=2 the cells b1, c1 and d1 should clear their contents.

this should happen identically for each row down the sheet.

currently i have one row working but can't get DIM to treat each row individually against the unique A? value e.g A3 should change row 3, etc.

Any help very gratefully received.

 

[dozibloke]

My bad, the identifier needs the With for the period. Let me update it.

 

[dozibloke]

Sub MM1()    Dim r As Long, lr As Long
    On Error GoTo ErrorCatch
        lr = Cells(Rows.Count, "C").End(xlUp).Row
        For r = 1 To lr
            If Range("C" & r).Value = 1 Then
                Range("H" & ":I" & r).ClearContents
            Else: Range("D" & ":G" & r).ClearContents
            End If
        Next r
ExitHandler:
        Exit Sub
ErrorCatch:
        MsgBox Err.Description, vbCritical
        Resume ExitHandler
End Sub[\CODE]

Gives - Method 'Range' of object '_Worksheet' failed

 

[dozibloke]

Sub MM1()    Dim r As Long, lr As Long
    On Error GoTo ErrorCatch
        lr = Cells(Rows.Count, "C").End(xlUp).Row
        For r = 1 To lr
            If Range("C" & r).Value = 1 Then
                Range("H" & ":I" & r).ClearContents
            Else: Range("D" & ":G" & r).ClearContents
            End If
        Next r
ExitHandler:
        Exit Sub
ErrorCatch:
        MsgBox Err.Description, vbCritical
        Resume ExitHandler
End Sub

 

[dozibloke]

in relation to providing H and D row numbers - does this not get collected through r when it counts from bottom of C up to 1 and returns r for the row?

 

[MARK858]

in relation to providing H and D row numbers - does this not get collected through r when it counts from bottom of C up to 1 and returns r for the row?

No, say your last row was row 20 then this..

Range("H" & ":I" & r).ClearContents

is saying this

Range("H:I20").ClearContents

which is an illegal range statement, you need the start row.

possibly you want..

Range("H" & r & ":I" & r).ClearContents

 

[dozibloke]

Thanks Mark, that makes sense.

Having looked at the code there should now be nothing which stops it from clearing the cells on the same row when the value of C changes from 1 to 2, or 2 to 1.

However, nothing changes at all, and I have macro's enabled. So I must have something wrong somewhere.

Sub MM1()Dim r As Long, lr As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
    For r = 1 To lr
        If Range("C" & r).Value = 1 Then
                Range("H" & r & ":I" & r).ClearContents
            Else: Range("D" & r & ":F" & r).ClearContents
        End If
    Next r
End Sub

 

[MARK858]

The code you posted in post #16 works fine for me as long you have cells with a value of exactly 1 in column C of the active sheet then columns H to I clear, else columns D to F clear.

 

[dozibloke]

i think it has something to me having some surrounding cells top and bottom of the row area that is being worked in - the surrounding cells are merged.

The non merged cell area starts from row 6 downwards and finishes at row 100 where there are some merged cells at the bottom also.

I therefore am setting the For r = 1 to lr
to For r = 6 to lr

and ditching the merged cells at the bottom.

 

[dozibloke]

That has sorted it nicely.

Thanks go to you and michael for your efforts and support on my learning journey. It is much appreciated. Its forums and people like you that keep me wanting to progress my VBA and understand how it all works.

Much appreciated, and I do hope you have a great weekend, what is left of it.

D

 

[dozibloke]