Macro to delete multiple cells in multiple sheets

[Smurfit-Stone]

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

 

[Datsmart]

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]

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.

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]

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.

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]

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

 

[excelnow]

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.

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]

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.

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]

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.

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]

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