Re: clear certain cells in a row (on 2 sheets) based on a cell value

[Allan8514096]

Re: clear certain cells in a row (on 2 sheets) based on a cell value

Hi all,
I have a workbook which contains the contents of medical packs. It has 2 sheets of relevance:
STRAT Consolidated -Contains bulk of data
Ordering -Contains copies of other sheet cells filtered to only show rows where sheet!"STRAT Consolidated" rowM=Y plus some new columns

What i need to achieve is the following:

IF STRAT Consolidated!T3 = Yes THEN (in cell dropdown list restricted to "Yes" or blank)
Message.Box "Are you sure? Completing this order will clear this information. This process is irreversible" YES/NO
IF No exit process
IF Yes proceed

THEN clear cells

Ordering!K3,L3,M3,N3
STRAT Consolidated!M3,T3
(note the cell clears itself at the end)
End

Note this only applies to Row3, part i have difficulty in finding info on is how to easily replicate this for (and keep limited to) each proceeding row 4-10,000).

If easier some sort of hyperlink type in cell button in STRAT Consolidated!T3 would be acceptable too

Im not sure if this is best achieved through formula, vba or another method.
I have a sample file to attach but havent found how.
Any help would be greatly appreciated!!!

 

[ZAX]

Re: clear certain cells in a row (on 2 sheets) based on a cell value

Add this code to the sheet's code by double clicking on "START Consolidated" in the VBA window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("T3")) Is Nothing Then
   If Target.Value = "Yes" Then
      m = MsgBox("Are you sure? Completing this order will clear this information." & _
      " This process is irreversible", vbYesNo)
      If m = vbYes Then
         Sheets("Ordering").Range("K3:N3").ClearContents
         Sheets("STRAT Consolidated").Range("M3,T3").ClearContents
      Else
         Exit Sub
      End If
   Else
      Exit Sub
   End If
Else
   Exit Sub
End If
End Sub

Plz try it on a sample data first.

ZAX

 

[Allan8514096]

Re: clear certain cells in a row (on 2 sheets) based on a cell value

Hi JAX,
First of all thankyou very much for your help the coding! It worked very well! The only issue is it only functions on row3. I need it to function on row4, row5, row6 etc but individually. So that as a paramedic restocks a line item they can put the 'Yes' in the corresponding T cell which then clears the same same cells of that row


IF T4= 'yes' THEN clear Sheets("Ordering").Range("K4:N4").ClearContents Sheets("STRAT Consolidated").Range("M4,T4").ClearContents

IF T5= 'yes' THEN clear Sheets("Ordering").Range("K5:N5").ClearContents Sheets("STRAT Consolidated").Range("M5,T5").ClearContents

IF T6= 'yes' THEN clear Sheets("Ordering").Range("K6:N6").ClearContents Sheets("STRAT Consolidated").Range("M6,T6").ClearContents

and so on (there is approx 7000 rows on one sheet)

So my question is do i have to copy this module 7000 times or is there a way to make it see the row number of T cell in which 'yes' is entered and propagate through out so there is only one copy of the code?

Thanks very much!

 

[ZAX]

Re: clear certain cells in a row (on 2 sheets) based on a cell value

Make sure that you activate the START Consolidated sheet before you run the macro on a SAMPLE data!

Sub DeleteCells()
For i = 3 To 7000
    If Range("T" & i).Value = "Yes" Then
       If msg = "" Then
          msg = i
       Else
          msg = msg & "," & i
       End If
    End If
Next i
m = MsgBox("Are you sure? Completing this order will clear this information on rows " & _
msg & ".This process is irreversible", vbYesNo)
If m = vbYes Then
   s = Split(msg, ",")
   For n = 1 To Len(msg)
       If Mid(msg, n, 1) = "," Then
          c = c + 1
       End If
   Next
   For l = 0 To c
       Range("K" & s(l) & ":N" & s(l)).Delete xlToLeft
       Sheets("Ordering").Range("M" & s(l) & ",T" & s(l)).Delete xlToLeft
   Next
Else
   Exit Sub
End If
End Sub

P.S:a Worksheet_Change code wouldn't help you so much so I wrote a -Manually run- macro.

ZAX

 

[Allan8514096]

Re: clear certain cells in a row (on 2 sheets) based on a cell value

ZAX,
Beautiful work, i had to adjust the reference ranges and swap delete to clearcontents

Code:

Sub DeleteCells()
For i = 3 To 7000
If Range("T" & i).Value = "Yes" Then
If msg = "" Then
msg = i
Else
msg = msg & "," & i
End If
End If
Next i
m = MsgBox("Are you sure? Completing this order will clear this information on rows " & _
msg & ".This process is irreversible", vbYesNo)
If m = vbYes Then
s = Split(msg, ",")
For n = 1 To Len(msg)
If Mid(msg, n, 1) = "," Then
c = c + 1
End If
Next
For l = 0 To c
Range("M" & s(l) & ",T" & s(l)).ClearContents
Sheets("Ordering").Range("K" & s(l) & ":N" & s(l)).ClearContents

Next
Else
Exit Sub
End If
End Sub



It works great I just attached it to a Submit button in the spread sheet toolbar. One last hiccup, when the user presses the button with no 'Yes' in the reference range you getting a debug error, could you please insert an escape line?

 

[ZAX]

Re: clear certain cells in a row (on 2 sheets) based on a cell value

Here's a code to help you:

Sub DeleteCells()
y = 0
For i = 3 To 7000
    If Range("T" & i).Value = "Yes" Then
       y = y + 1
       If msg = "" Then
          msg = i
       Else
          msg = msg & "," & i
       End If
    End If
Next i
If y = 0 Then
   Exit Sub
End If
m = MsgBox("Are you sure? Completing this order will clear this information on rows " & _
msg & ".This process is irreversible", vbYesNo)
If m = vbYes Then
   s = Split(msg, ",")
   For n = 1 To Len(msg)
       If Mid(msg, n, 1) = "," Then
          c = c + 1
       End If
   Next
   For l = 0 To c
       Range("M" & s(l) & ",T" & s(l)).Delete xlToLeft
       Sheets("Ordering").Range("K" & s(l) & ":N" & s(l)).Delete xlToLeft
   Next
Else
   Exit Sub
End If
End Sub

So my question is do i have to copy this module 7000 times?

Dear Allan,
That's why the VBA was built,To simplify things for you and save your time and effort!
ZAX