.ClearContents and .Value = "" both not working

jamesblack90

New Member
Joined
Jun 9, 2015
Messages
44
Hey guys, am trying to create a simple macro that loops through the headings in row 16, if one of them is blank, then clear and lock all applicable cells in that column.

The locking and unlocking work great! But none of the contents of the cells are being cleared. I've tried .ClearContents and .Value = "" but it just retains the data (if any)
Any ideas?

Code:
Private Sub lock_cells()

Dim i As Integer
Dim j As Integer


i = 19
j = 3


Application.ScreenUpdating = False
ActiveSheet.Unprotect


Do While j < 13
    
    If Cells(16, j).Value = "" Then
    
        Do While i < 22
            Cells(i, j).ClearContents
            Cells(i, j).Locked = True
            i = i + 2
        Loop
        
        i = 24
        Do While i < 33
            Cells(i, j).ClearContents
            Cells(i, j).Locked = True
            i = i + 2
        Loop
        
        i = 40
        Do While i < 43
            Cells(i, j).ClearContents
            Cells(i, j).Locked = True
            i = i + 2
        Loop
        
        i = 47
        Do While i < 60
            Cells(i, j).ClearContents
            Cells(i, j).Locked = True
            i = i + 2
        Loop
    End If
    
    i = 19
    
    If Not Cells(16, j).Value = "" Then
        Do While i < 22
            Cells(i, j).Locked = False
            i = i + 2
        Loop
        
        i = 24
        Do While i < 33
            Cells(i, j).Locked = False
            i = i + 2
        Loop
        
        i = 40
        Do While i < 43
            Cells(i, j).Locked = False
            i = i + 2
        Loop
        
        i = 47
        Do While i < 60
            Cells(i, j).Locked = False
            i = i + 2
        Loop
    End If
    
    j = j + 3
    
Loop


Application.ScreenUpdating = True
ActiveSheet.Protect


End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I don't understand "Heading's in Row 16". Do you mean values in Row 16.
Headings which are actually values are normally in Row 1.
 
Upvote 0
Svendiamond I don't understand your response?

My Answer Is This: Sorry, yes, on row 16 the user can input names into cells C16, F16, I16 and L16, (so for the user, these are like headings) my code needs to check if these are blank or not and lock or unlock the (non-contiguous) cells below them.
The locking and unlocking work fine, so I know my i and j loops are fine, but I just can't seem to get it to clear the cells if the name is deleted from row16
 
Upvote 0
How do you know the Locking (not the UNlocking, just the Locking) part is working?
Are you going just by the state of the cells locked property After the code runs?
Perhaps the cell was already locked before the code ran..

Use F8 to step through the code and watch as it happens.

I suspect either
A) The 'Blanks' in row 16 are not 'really' blank. Perhaps they contain a space or some other strange character.
so this part is always FALSE
If Cells(16, j).Value = "" Then
And the clear contents command never actually occurs.

or
B) You have a Worksheet_Change event code that is RE-Populating the cells after they cleared.


Step through the code using F8 and observe what is actually happening.
 
Upvote 0
Hi Jonmo,

Thanks for your insight, I know the cells are locking/unlocking because if I delete the values in row 16 they lock and if I type something in they then unlock. I don't have any worksheet_change() sub on this particular sheet and the sub which populates my row 16 is called before this sub. I don't know when or where you want me to hit F8 unfortunately (This is day 5 of me learning vba). I am on excel mac 2010. Also if I put message boxes in each individual do while i < x loop then they all display.

I'm using this similar loop function as a "Reset Form" macro, and it deletes everything correctly except F19,22, H19,22 I19,22 and L19,22 I'm so stumped. :/

How do you know the Locking (not the UNlocking, just the Locking) part is working?
Are you going just by the state of the cells locked property After the code runs?
Perhaps the cell was already locked before the code ran..

Use F8 to step through the code and watch as it happens.

I suspect either
A) The 'Blanks' in row 16 are not 'really' blank. Perhaps they contain a space or some other strange character.
so this part is always FALSE
If Cells(16, j).Value = "" Then
And the clear contents command never actually occurs.

or
B) You have a Worksheet_Change event code that is RE-Populating the cells after they cleared.


Step through the code using F8 and observe what is actually happening.
 
Upvote 0
Stepping Through code is a vital tool for troubleshooting VBA Code.
You can watch each step as it happens to observe the results while the code is running.

Arrange your screens so you can see both Excel Window and VBA window at the same time.
Put your cursor anywhere inside the Sub in the VBA window.
And just start pressing F8
It will execute the code 1 line at a time.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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
Back
Top