clearing a cell based on dropdown value

D

dozibloke

New Member
Joined
Apr 1, 2017
Messages
14
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
what have you coded so far...please post what you have already
 
Upvote 0
Can you clarify this?
You wrote:
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

Is it really that B, C, and D are to have their contents cleared when the cell in column A of that row = 2
and
E, D, and E are to have their contents cleared when the cell in column A of that row = 1

Really? E, D, and E?
Do you mean E, F, and G? Or some other columns? Or will D and E always have their contents cleared no matter what gets entered into column A.
 
Upvote 0
Apologies Tom, it was late last night when I posted, and I had been at it for a while:


column A has identical cells which are a dropdown with value 1 and 2.
next to A1 drop down I have b1, c1 and d1 which are text entry cells which a user can put values in if A1=1
I also have e1, f1 and g1 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 (which is valid), if they then change the A1=2 the cells b1, c1 and d1 should clear their contents. Similarly if a user choses A1=2 and then puts values into e1, f1 and g1 and then changes the pull down to A1=1 the e1, f1 and g1 cells 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.
 
Upvote 0
Is this heading in the right direction....

Code: 
Sub MM1()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 1 To lr
        If Range("A" & r).Value = 1 Then
                Range("E" & ":G" & r).ClearContents
            Else: Range("B" & ":D" & r).ClearContents
        End If
    Next r
End Sub
 
Upvote 0
Michael M said:
Is this heading in the right direction....

Code: 
Sub MM1()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 1 To lr
        If Range("A" & r).Value = 1 Then
                Range("E" & ":G" & r).ClearContents
            Else: Range("B" & ":D" & r).ClearContents
        End If
    Next r
End Sub
Click to expand...


Thanks for the very quick response, what a star!

I have given that a whirl and get a method range of object _worksheet failed error.

I have changed your original code slightly as I have added some more cells in the spreadsheet since last night.
B6 is a pulldown with two text items which are 'accrued' and 'used' . I have put C6 as a IF B6 = accrued then 1 else 2, and then related your code to C6 for the 1 or 2 values.

D6:G6 are valid if C6=1, and clear H6:I6
H6:I6 are valid if C6= 2, and clear D6:G6

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
 
Upvote 0
Sub MM1()
Dim r As Long, lr As Long
On Error GoTo ErrorCatch
With ActiveSheet
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 With
End Sub
 
Upvote 0
You are missing a lot of periods/fullstops that you need with the with statement you have put in (and I don't know why you have put in the with statement with activesheet).

Code: 
Sub MM1()
    Dim r As Long, lr As Long
    On Error GoTo ErrorCatch
    With ActiveSheet
        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 With
End Sub
and both the letters in red need row numbers...
Code: 
.Range("[COLOR="#FF0000"]H[/COLOR]" & ":I" & r).
Else: .Range("[COLOR="#FF0000"]D[/COLOR]" & ":G" & r).
 
Last edited:
Upvote 0
Thanks Mark,

I have removed the active sheet comment for the time being - i just wanted this to work on one sheet at a time, depending on the user as there are 30 sheets 1 per user. I matters not really as you quite rightly say - as the logic applies to all sheets regardless of if they were active or not.

Having tried the updated code I get an invalid or unqualified reference for the .Cells(rows.Count line.

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
 
Upvote 0
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
condition formatting based on cell value
Replies
8
Views
770
mwvirk
M
S
  • Solved
Macro to add quotes, commas, transpose and select the transposed rows
2
Replies
14
Views
456
Peter_SSs
Peter_SSs
A
  • Solved
Excel formula does not return expected result when encountering a cell that includes text in a formula reference
2
Replies
11
Views
615
Joe4
Joe4
E
  • Solved
Reverse Data in Row Cells
Replies
3
Views
239
elkerpride
E
J
  • Solved
Dynamic Dropdown using Data Validation list .
2
Replies
12
Views
521
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,940
Messages
6,122,361
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top