Shorten Code?

**Mhz** · 04-21-2007, 10:49 AM

Hi,

jasoncw recently helped me with a neat code to replace my long coding, I
have a similar request to see if this code can be shortened.

The code is to clear contents from C8:L107 on multiple identical sheets
'DAC1F1 to DEC10F10' which is the equivalent of 500 worksheets to be cleared, but identical Range being cleared on all of them.

The code is presented below:

HTML Code:

Thnaks for any attempted help on this...

**Leith Ross** · 04-21-2007, 11:47 AM

Hello Mhz,

It appears that you are going through every worksheet in the workbook. This code will clear the specified range in every worksheet of the workbook.

Sub CLR_ALL()
'
' CLR_ALL Macro
Dim pas As String
Dim Wks As Worksheet

Str1 = InputBox("WARNING!  THIS WILL DELETE ALL FILES IN EVERY DRAWER!  ARE YOU SURE?  ENTER THE PASSWORD TO PROCEED or CANCEL")

If Str1 = "" Then Exit Sub
If Str1 <> "12345" Then Exit Sub

For Each Wks In Worksheets
Wks.Range("C8:L107").ClearContents
NextWks

    MsgBox "All Drawer Files Have Been Deleted, Click OK To Proceed."
    Sheets("START").Select
   
End Sub

Sincerely,
Leith Ross

**antoka05** · 04-21-2007, 11:48 AM

Try with this code:

Sub Macro1()
   Application.ScreenUpdating = False
   
   For a = 1 To 5
      For b = 1 To 10
         For c = 1 To 10
            mySheetName = "D" & Chr(64 + a) & "C" & b & "F" & c
            
            With Sheets(mySheetName)
               .Range("C8:L107").ClearContents
               .Range("C8").Select
            End With

         Next
      Next
   Next
            
   MsgBox "All Drawer Files Have Been Deleted, Click OK To Proceed."
   Sheets("START").Select
    
   Application.ScreenUpdating = True


End Sub

You can alse use this shorter code for all sheets with name beginning for 'D':

Sub Macro2()
   Application.ScreenUpdating = False
   
   For Each ws In ThisWorkbook.Sheets
      If Sheet.Name Like "D*" Then
         With Sheets(mySheetName)
               .Range("C8:L107").ClearContents
               .Range("C8").Select
         End With
      End If
   Next
            
   MsgBox "All Drawer Files Have Been Deleted, Click OK To Proceed."
   Sheets("START").Select
    
   Application.ScreenUpdating = True
End Sub

Regards,
Antonio

**Mhz** · 04-21-2007, 12:48 PM

Hello Leith and antoka,

BRILLIANT!

...

Leith the only exception is that I do have other sheets in the workbook,
about another 30 to be exact, so the coding would have erased unspecified
data. But thanks for the Input.

Anatoka05, Yes, a bit confusing for me with the letter arrangements and all
but none the less Works GREAT!

However, Unfortunately for me, The Second
coding suggesting Worksheets Beginning with 'D' couldn't work for me due
to the fact I have about 20 other sheets starting with 'D'

But
This is very useful for a latter project. Thanks Alot.

Thanks To You Both for Your Unique Inputs.. It's Much Appreciated!

**Leith Ross** · 04-21-2007, 03:58 PM

Hello Mhz,

Skipping sheets is easy. You probably don't have many to skip. I have placed a section in the code where you can add the names of the sheets you want skipped. It's marked so you can put the names into the code.

Sub CLR_ALL()
'
' CLR_ALL Macro
Dim pas As String
Dim Wks As Worksheet

Str1 = InputBox("WARNING!  THIS WILL DELETE ALL FILES IN EVERY DRAWER!  ARE YOU SURE?  ENTER THE PASSWORD TO PROCEED or CANCEL")

  If Str1 = "" Then Exit Sub
  If Str1 <> "12345" Then Exit Sub

    For Each Wks In Worksheets
      Select Case Wks
        Case Is = "Sheet1", "Sheet2"
          'Do nothing - this skips over the listed sheets
        Case Else
          Wks.Range("C8:L107").ClearContents
      End Select
    NextWks

    MsgBox "All Drawer Files Have Been Deleted, Click OK To Proceed."
    Sheets("START").Select
   
End Sub

Just add the names of your sheets, separated by commas, where you see the code in red.

Sincerely,
Leith Ross

**Mhz** · 04-21-2007, 06:18 PM

Hey, Thanks Alot Ross,

This Is surely Something That is and will be Useful for me. Didn't
Know such a Bulky Coding Could be Reduced To such a Meaningful short
Code Like The One You Presented Here. Much Appreciated!

Going
To Use it Right Away.. Thanks..