---

Loop All Worksheets/Sheets in Workbook & Apply Code

Got any Excel/VBA Questions? Excel Help.

 

Loop Through All Sheets

The code below loops through all Worksheets in the active Workbook and clears cells with the Style name specified by the user. It uses an input box to collect the Style name. It also incorporates a select case statement to exclude/include specified Sheets by  codename. It also uses the UserInterFaceOnly argument of the Sheet Protection Method, so while code can run on protected sheets, normal users cannot change locked cells.

The other important element is the use of the  special cells method to ensure only the cell the user wants (Formulae/Constants or both) are affected. This is asked of the user via the MsgBox Function.

The code is optimised for both speed and efficiency, which should not be confused with the shorter the code the faster and more efficient it is.

Naturally the code uses Styles only as an example, it could any code that uses cell Properties, Objects and Methods.

Sub ClearCellsWithStyleX()

Dim strStyleName As String

Dim lClearFormulae As Long

Dim wsheet As Worksheet

Dim rCcells As Range, rFcells As Range

Dim rLookin As Range, rCell As Range

Dim xlCal As XlCalculation





        'Collect Style name. Default is 1st in Index

        strStyleName = InputBox("Type the style name", _

            "CLEAR CELL WITH STYLE...", ThisWorkbook.Styles(1))

        If strStyleName = vbNullString Then Exit Sub 'Cancelled

        

        'Find out if formulae should be cleared

        lClearFormulae = MsgBox("Clear formulae cells with the Style " _

            & strStyleName, vbYesNo + vbQuestion)



        'Turn off error trapping

        On Error Resume Next

        'Pass the Calculation mode

        xlCal = Application.Calculation

        'Put calculation into manual

        Application.Calculation = xlCalculationManual

        'Turn off screenupdating

        Application.ScreenUpdating = False

    

        'Loop through all Worksheets

        For Each wsheet In Worksheets

           Select Case UCase(wsheet.CodeName)

                'List any sheets (UPPER CASE) to exclude here.

                Case "SHEET1", "SHEET5"

                    'Do optional code here

                Case Else

                    'Protect/reprotect so only code can affect _

                    locked cells.

                    wsheet.Protect Password:="Secret", _

                        UserInterFaceOnly:=True

                    With wsheet.UsedRange

                        'Set range variable to all constants

                        Set rCcells = .SpecialCells(xlCellTypeConstants)

                            'Formulae cells to be cleared.

                            If lClearFormulae = vbYes Then

                                'Set range variable to all formula cells.

                                Set rFcells = .SpecialCells(xlCellTypeFormulas)

                            End If

                    End With



                     'Reduce down range to look in

                    If rCcells Is Nothing And lClearFormulae = vbYes Then

                        Set rLookin = rFcells.Cells 'formulas

                    ElseIf rFcells Is Nothing Then

                        Set rLookin = rCcells.Cells 'constants

                    Else

                        Set rLookin = Application.Union(rFcells, rCcells) 'Both

                    End If

                    

                    'Loop for each cell in required range

                    For Each rCell In rLookin

                        'Change coding here to suit

                        If rCell.Style = strStyleName Then rCell.ClearContents

                    Next rCell

            End Select

        Next wsheet

        

        'Set calculation, screenupdating and error trapping back.

        Application.Calculation = xlCal

        Application.ScreenUpdating = True

        On Error GoTo 0

        

End Sub

 

See also:

Used to Loop Through a Collection or Array

For Loop

For Loop Step

Convert Excel Formulas from Relative to Absolute

Custom Excel Formulas - User Defined Functions/Formulas

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. 

Gallery

---

• 
• 
• 
• 
• 
•