Clearing all unlocked cells, but want to eliminate or repopulate specific Cells

JustJerry2C

New Member
Joined
Aug 21, 2017
Messages
16
I have some existing code that works exactly as it should:
VBA Code:
 'Clear Vocab Entries
                With Sheets(VocabSpell.Name)
                    Set rClear = Nothing
                    For Each r In Sheets(VocabSpell.Name).UsedRange
                        If r.Locked = False Then
                            If rClear Is Nothing Then
                                Set rClear = r
                            Else
                                Set rClear = Union(rClear, r)
                            End If
                        End If
                    Next r
                    rClear.ClearContents
                End With

What I can't seem to make work, is that I want three specific cells to NOT be deleted, or repopulated with existing values, those being cells B70, 71 and 72.

I tired this for starters trying to assign a temporary value, but it does not work

VBA Code:
'Clear Vocab Entries
                With Sheets(VocabSpell.Name)
                    TEMP=Range("B70").Value
                    Set rClear = Nothing
                    For Each r In Sheets(VocabSpell.Name).UsedRange
                        If r.Locked = False Then
                            If rClear Is Nothing Then
                                Set rClear = r
                            Else
                                Set rClear = Union(rClear, r)
                            End If
                        End If
                    Next r
                    rClear.ClearContents
                   Range("B70").Value = TEMP
                End With

Any suggestions would greatly be appreciated!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Ok, I have tried so many things and can't figure this out. Latest:

VBA Code:
Public Function DataDelete()

'This Sub will clear all content from grading sheets
'As of 2/6/2019, Student names on control sheet not included
    
Dim r As Range, rClear As Range
Set rClear = Nothing

'Clear Vocab Entries
                With Sheets(VocabSpell.Name)
                    Set rClear = Nothing
                    For Each r In Sheets(VocabSpell.Name).UsedRange
                        If r.Locked = False Then
                            If rClear Is Nothing Then
                                Set rClear = r
                            Else
                                Set rClear = Union(rClear, r)
                            End If
                        End If
                    Next r
                    rClear.ClearContents
                End With

 MsgBox "All Grades and Student Names Have Been Deleted", vbOKOnly + vbInformation, "Grades/Student Names Deleted"
                
                Application.ScreenUpdating = True

                Range("B3").Select
                
                Call AddNames

End Function

Public Function AddNames()

    Worksheets(VocabSpell.Name).Range("B70") = Worksheets(ControlSheet.Name).Range("B3").Value

End Function

If I debug and run the AddNames by itself, it will repopulate and do what I want it to do. However, it won't run with the other code, even if I remove the Call AddNames and replace with the code line in the AddNames function. I am stumped.
 
Upvote 0

Forum statistics

Threads
1,215,084
Messages
6,123,029
Members
449,092
Latest member
ikke

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