Remove or Clear Data

Solved/Closed
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 6, 2015 at 05:18 AM
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 6, 2015 at 02:46 PM
Hello,
I am grateful if this query be solved.

If user Enter any value in C2 and also user enter "Best" or "Worst" in Cell D2, how it may clear the value of Cell C2.

Thanks & regards

4 replies

smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 6, 2015 at 12:37 PM
Hi
it shows an error at
rangeToCheck.Offset(0, -1) = vbNullString
Either it enter Best or Worst

Thanks for cooperation
1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 6, 2015 at 12:47 PM
works for me. May be you have merged cells or some thing like that
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 6, 2015 at 09:53 AM
you would need to have an event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rangeToCheck As Range

Set rangeToCheck = Range("D2")
If Not Intersect(Target, rangeToCheck) Is Nothing Then
rangeToCheck.Offset(0, -1) = vbNullString
End If
End Sub
0
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 6, 2015 at 11:45 AM
Hi
Your code worked anything enter in cell D2 it clears the data in cell C2
But I requested that it works only for two Words i.e. "Best" & "Worst".
One more thing these two words can be enter within this range D2 to D1000
then it clears data where ever the above mention words enter within the specified range.

Thanks
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 6, 2015 at 12:23 PM
Well my purpose was to give you an idea that you could have expanded upon. Here is more

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rangeToCheck As Range
Dim rngTarget As Range

Set rangeToCheck = Range("D2:D1000")
Set rngTarget = Intersect(Target, rangeToCheck)

If Not rngTarget Is Nothing Then
Application.EnableEvents = False
For Each rangeToCheck In rngTarget
If rangeToCheck = "Best" Then
rangeToCheck.Offset(0, -1) = vbNullString
ElseIf rangeToCheck = "Worst" Then
rangeToCheck.Offset(0, -1) = vbNullString
End If
Next
Application.EnableEvents = True
End If

End Sub
0
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 6, 2015 at 01:06 PM
There is no merging cells
I have tried in many ways
the table consist of A to F Columns
it contains data in all column
I have remove data one by one by one from A, B, E, & F columns then copy code to Sheet and apply but still shows an error.

Thanks
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Nov 6, 2015 at 01:15 PM
Only thing i can suggest would be
1. create a new workbook
2. paste the code
3. populate some values in column C
4. try typing various values in column D to see if it works there or not
0
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1 > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Nov 6, 2015 at 01:26 PM
its Very Strange it worked after following your steps
Thanks
Great
One thing more If I have to clear data of Column B
what changes it would occur in your Perfect code
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768 > smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017
Nov 6, 2015 at 01:54 PM
Here is the same with comments. Try to understand what code does. Making mistake is part of learning

'Target refers to a cell or a range of cell that were modified
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rangeToCheck As Range 'a variable to store what range is to be checked for change
Dim rngTarget As Range ' a variable that would hold cells that are were changed and are in the range in which interested

Set rangeToCheck = Range("D2:D1000") 'interested to trap changed in column D

'find what is the common range between range that is changed and range in which interested
'if there is nothing common then NOTHING is returned, otherwise it is a range of cells that are common
Set rngTarget = Intersect(Target, rangeToCheck)

'check if there is some thing that is common
If Not rngTarget Is Nothing Then

' this is the disable the events
Application.EnableEvents = False

'for each cell in the common range
For Each rangeToCheck In rngTarget

'if the cell value is Best
If rangeToCheck = "Best" Then
'from cell address, change the cell value one column to left
rangeToCheck.Offset(0, -1) = vbNullString
ElseIf rangeToCheck = "Worst" Then
'from cell address, change the cell value one column to left
rangeToCheck.Offset(0, -1) = vbNullString
End If
Next
'enable the events back as all cells were processed
Application.EnableEvents = True
End If

End Sub
0
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Nov 6, 2015 at 02:46 PM
Thanks For Your Explanation which clarify my concept
I am Grateful to you
0