Simple ClearContents not working

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
I can't figure out why this won't work. Can someone take a look?

Code:
Private Sub ClearContents()
Worksheets("Costing").Activate
If Cells(7, 5).Value = "Other %" Then Cells(7, 7).ClearContents
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,714
Office Version
  1. 2013
Platform
  1. Windows
You do understand:

Code:
[LEFT][COLOR=#333333][FONT=monospace]If Cells(7, 5).Value = "Other %" Then Cells(7, 7).ClearContents
[/FONT][/COLOR][/LEFT]

Means if Row(7) column(5) And then clear Row(7) Column(7)

These are not the same.
 

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
Yes - I want one cell cleared based on the value in a different cell.
Can you help with this?
-Colin
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,436
Office Version
  1. 365
Platform
  1. Windows
What is the value in E7 on the Costing sheet?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,470
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Colin, check that the string in E7 has no leading or trailing spaces, also check that it is only one space between the r and the %.
 

ColinCoady

New Member
Joined
Mar 21, 2019
Messages
35
The value in E7 is from a Data validation list with 5 choices. One of the choices is "Other %"
I changed "Other %" to "Other%" (no space) and changed the code accordingly. Still doesn't want to work.
Still missing something - just not sure what. Any thoughts?
Thanks MARK858 and Fluff
-Colin
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,470
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

None from me at the moment as long as it is G7 being cleared.

Just out of interest can you copy and paste (Please copy/paste it, don't retype it) the result that appears in your immediate window if you run the code below.

Code:
Sub testit()
Debug.Print "| &"; Cells(7, "E").Value; "& |", Len(Cells(7, "E").Value)
End Sub
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,470
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Then I am afraid I have no ideas at the moment (if you don't have any other code taking effect and no protection, which would have produced an error) as your code is clearing G7 for me.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test following

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$E$7" Then Exit Sub
  If Target = "Other %" Then Target.Offset(0, 2).ClearContents
End Sub

Hope this will help
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,440
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top