Remove or Clear Data

Solved/Closed

smuneeb Posts 67 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Nov 6, 2015 at 05:18 AM
smuneeb Posts 67 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

Remove or Clear Data
Tmobile data check - Guide
Ubuntu remove password - Guide
Remove recent files windows 11 - Guide
How to remove number from blacklist - Guide
Transfer data from one excel worksheet to another automatically - Guide

4 responses

Answer 1 / 4

smuneeb Posts 67 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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 6, 2015 at 12:47 PM

works for me. May be you have merged cells or some thing like that

Answer 2 / 4

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

Answer 3 / 4

smuneeb Posts 67 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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

Answer 4 / 4

smuneeb Posts 67 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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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

smuneeb Posts 67 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1 > rizvisa1 Posts 4478 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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > smuneeb Posts 67 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

smuneeb Posts 67 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

Similar discussions

Can't remove threat with Windows Defender

Hosted app data will not clear

AMD data change update new data to DMI

How to undo "remove link" in bio?

Similar discussions

Subscribe To Our Newsletter!