Macro to delete multiple cells in multiple sheets

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
Hello Board,

I need a macro that will delete the contents of multiple cells in multiple sheets. They are cells that users will enter data, but on close I would like those cells to be cleared. Can anyone tell me how I can do this without clearing essential data. I have highlighted the data entry cell in yellow.

Thanks in advance
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
While this is "Do-able", the code would take some time to go through every cell in every worksheet looking for yellow cells.
Why not just not allow any changes to the workbook at all? You could do that with code that does not allow saving the workbook using the original name. A search of this forum should get you a number of ideas along that line.
 

shaneherron

Board Regular
Joined
Nov 3, 2009
Messages
51
Datsmart,
I have a code that will do what you are asking on one condition. The cells that you DO NOT want to clear must me locked. The cells that you DO want to clear must be unlocked. As long as that is true then this code will work for you.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
        Dim myrange As Range
        Dim cell As Range
        Dim found As Range
        Set myrange = Sheet1.UsedRange
            For Each cell In myrange
                If cell.Locked = False Then
                    If found Is Nothing Then
                        Set found = cell
                    Else
                        Set found = Union(cell, found)
                    End If
                End If
            Next cell
        found.Select
        Selection.ClearContents
Next WS
End Sub
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
From your description, it is not clear which are essential and which are to be cleared . this code specifies the range to be cleared.
Code:
Sub stone()
Dim a As Long
    For a = 1 To Sheets.Count
        Worksheets(a).Range("A25:G45").ClearContents
    Next a
MsgBox "Complete"
End Sub
ravi
 

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485

ADVERTISEMENT

I want to Thank everyone to posted a reply to this thread. It all worked great. Thanks again!
 

excelnow

Board Regular
Joined
Nov 17, 2009
Messages
106
I was about to ask this question, thanks to Smurfit he asked before me. I think the code below will work for some specified sheets and cell ranges, and that is what I wanted. Thanks ravishankar.

Hi
From your description, it is not clear which are essential and which are to be cleared . this code specifies the range to be cleared.
Code:
Sub stone()
Dim a As Long
    For a = 1 To Sheets.Count
        Worksheets(a).Range("A25:G45").ClearContents
    Next a
MsgBox "Complete"
End Sub
ravi
 

saud968

New Member
Joined
Feb 10, 2016
Messages
2

ADVERTISEMENT

Hi
From your description, it is not clear which are essential and which are to be cleared . this code specifies the range to be cleared.
Code:
Sub stone()
Dim a As Long
    For a = 1 To Sheets.Count
        Worksheets(a).Range("A25:G45").ClearContents
    Next a
MsgBox "Complete"
End Sub
ravi

Ravi can we get this code to clean only specific sheets and range, as it clear all sheets in the workbook.
 

saud968

New Member
Joined
Feb 10, 2016
Messages
2
Ravi can we get this code to clean only specific sheets and range, as it clear all sheets in the workbook.

Hi
From your description, it is not clear which are essential and which are to be cleared . this code specifies the range to be cleared.
Code:
Sub stone()
Dim a As Long
    For a = 1 To Sheets.Count
        Worksheets(a).Range("A25:G45").ClearContents
    Next a
MsgBox "Complete"
End Sub
ravi

Ravi i was using this loop VBA however now i have more sheets in the workbook, so i dont want to delete other sheets.

Sub Button2_Click()
' using for each loop
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.Range("2:10000").Delete
Next


' using for loop with an iterator
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).Range("2:10000").Delete
Next i


' using do while loop
i = 1
Do While i <= Sheets.Count
Sheets(i).Range("2:10000").Delete
i = i + 1
Loop
End Sub
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Ravi i was using this loop VBA however now i have more sheets in the workbook, so i dont want to delete other sheets.

Sub Button2_Click()
' using for each loop
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
ws.Range("2:10000").Delete
Next


' using for loop with an iterator
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).Range("2:10000").Delete
Next i


' using do while loop
i = 1
Do While i <= Sheets.Count
Sheets(i).Range("2:10000").Delete
i = i + 1
Loop
End Sub
You need to specify which sheets you want untouched and which sheets you want select cells deleted. in the macro code
Ravishankar
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,266
Members
416,963
Latest member
samfuge

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