VBA: Clearing contents from many sheets with the same range.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
Hey Excel Gurus,

How are you? Please if someone could guide me here. I want to clear all contents from cells C5:C100 from all sheets of the workbook except for two sheets namely "HSheet" and "SS"
The range consists of data validation and other formatting which I'd like to keep but remove just the values. I am sure it can be done via vba.

Will appreciate a lot.. anyone?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,

This can be done without VBA.
Select all the sheets of interest, then select the range C5: C100 and then delete. Ready. Then click on an unselected sheet.
 
Upvote 0
Hi,

This can be done without VBA.
Select all the sheets of interest, then select the range C5: C100 and then delete. Ready. Then click on an unselected sheet.
I would still be requiring a macro. I mean for the end user. The inventories needs to be closed for the current month by clearing that range.
 
Upvote 0
Try this:
Not sure about Data Validation.
VBA Code:
Sub Clear_Range()
'Modified  11/20/2020  1:53:00 AM  EST
Application.ScreenUpdating = False
Dim ans As Long
ans = ThisWorkbook.Sheets.Count
Dim i As Long


For i = 1 To ans
    If Sheets(i).Name <> "HSheet" And Sheets(i).Name <> "SS" Then Sheets(i).Range("C5: C100").ClearContents
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:
Not sure about Data Validation.
VBA Code:
Sub Clear_Range()
'Modified  11/20/2020  1:53:00 AM  EST
Application.ScreenUpdating = False
Dim ans As Long
ans = ThisWorkbook.Sheets.Count
Dim i As Long


For i = 1 To ans
    If Sheets(i).Name <> "HSheet" And Sheets(i).Name <> "SS" Then Sheets(i).Range("C5: C100").ClearContents
Next
Application.ScreenUpdating = True
End Sub
Thank you!
You made my day . And yes data validation will work.
 
Upvote 0
If you had a lot of sheet names you wanted excluded you could use this:
Just add more names:
VBA Code:
Sub Clear_Range()
'Modified  11/20/2020  2:26:37 AM  EST
Application.ScreenUpdating = False
Dim ans As Long
ans = ThisWorkbook.Sheets.Count
Dim i As Long

    For i = 1 To ans
        Select Case Sheets(i).Name
            Case "HSheet", "SS"
                'do nothing
            Case Else
                Sheets(i).Range("C5: C100").ClearContents
        End Select
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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