Got any Excel/VBA Questions? Excel Help.
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
|Used to Loop Through a Collection or Array|
|For Loop Step|
|Convert Excel Formulas from Relative to Absolute|
|Custom Excel Formulas - User Defined Functions/Formulas|