Clear Cells based on User Input Box to define Row Number

[Lisa W]

Hi I have the code below which works well, however I need it to be based on both columns C & J. So if "C" is empty / blank then Clear Contents of "J". I have tried several ways to add "J" into the range without success. Any help would be greatly appreciated:

Public Sub RAG_Click()
'To Clear RAG Status and re-enter'
Dim x As Integer
ActiveSheet.Unprotect
x = InputBox("Please Enter the Row Number")
Range("C" & x).ClearContents
ActiveSheet.Unprotect
End Sub

 

[Joe4]

Welcome to the Board!

If you want to clear column J if column C is blank, then change this line:

Range("C" & x).ClearContents

to this:

If Range("C" & x) = "" Then Range("J" & x).ClearContents

 

[Lisa W]

Thanks Joe, this is working for Column J but now not clearing Column C if not empty/blank. The Values for both of these cells are Text, controlled by Data Validation if that makes a difference. (R, A, G, U)?

 

[Joe4]

It is not quite clear what you want.

Your original code was clearing column C.
But then you said clear column J IF column C is blank.
So, do you want to check column C or not?
If you clear column C, of course it will be blank.

If you want to clear BOTH columns C and J, and not bother checking them first, then just add another line to your code like the first, i.e.

Public Sub RAG_Click()
'To Clear RAG Status and re-enter'
Dim x As Integer
ActiveSheet.Unprotect
x = InputBox("Please Enter the Row Number")
Range("C" & x).ClearContents
Range("J" & x).ClearContents
ActiveSheet.Unprotect
End Sub

 

[dmt32]

Hi,
try

Public Sub RAG_Click()
    'To Clear RAG Status and re-enter'
    Dim x           As Variant
    ActiveSheet.Unprotect
   
    Do
        x = InputBox("Please Enter the Row Number", "Enter Row")
    Loop Until IsNumeric(x)
    'cancel pressed
    If StrPtr(x) = 0 Then Exit Sub
   
    With Cells(CLng(x), "C")
          .ClearContents
         .Offset(, 7).ClearContents
    End With
   
    ActiveSheet.Unprotect
End Sub

Dave

 

[Lisa W]

Sorry Joe I wasn't very clear reading it back so either Column C or Column J would contain text not both on same row.

 

[Joe4]

Does the last code I posted do what you want?
If not, please explain how it is not doing what you want.

 

[Lisa W]

Thanks Joe, yes it works not sure why it didn't when I tried that prior to posting. Thank you very much for your help.

 

[Joe4]

You are welcome.
Note that when marking the solution, you want to mark the post that actually contains the solution.
I have re-marked it for you, as the post of mine that you marked did not have the solution (code) in it.

 

[Lisa W]

Ahh, will do. Do I need to do anything else?