Remove or Clear Data [Solved/Closed]

Report
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
-
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

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

Thanks for cooperation
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
works for me. May be you have merged cells or some thing like that
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1 >
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767 >
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017

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
Posts
77
Registration date
Saturday September 5, 2015
Status
Member
Last seen
March 8, 2017
1
Thanks For Your Explanation which clarify my concept
I am Grateful to you

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!