Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter July 2007

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

Excel Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

Get our Complete Excel Course at 50% Off and Give Yourself Free Support & Help 24hrs a Day, 7 Days a Week, 365 Days of the Year! get any license at half price simply by registering here

Cyclops Stock Market Monitor. Analyze correlations between foreign exchange and stock market trends

All Software Many with free give-aways!

EXCEL TIPS AND TRICKS

NIFTY FORMULAS FROM A FORMULA GURU

These formulas and the ones in the download are fromKrishnakumar a OzMVP on our help forum.

Last Text Value In a List

=LOOKUP(REPT("z",255),A:A)

Last Numeric Entry in a List

=LOOKUP(9.99999999999E+307,B:B)

Last Entry in a List With Numbers and Text

=INDEX(C:C,MAX(MATCH("zzzzzzzzzz",C:C),MATCH(9.9999E+307,C:C)))

Download examples of these and many more!

EXCEL VBA TIPS AND TRICKS

Lot's More: Excel VBA. See Also:Loop Through a Folder of Workbooks || Add Worksheets in Month Order ||Add Worksheets in Numeric Order || Sort Excel Sheets/Worksheets

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 anInputBox to collect the Style name. It also incorporates aSelect Case Statement to exclude/include specified Sheets byCodeName. 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 theSpecialCells 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 optimized 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 StringDim lClearFormulae As LongDim wsheet As WorksheetDim rCcells As Range, rFcells As RangeDim rLookin As Range, rCell As RangeDim 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

Got any Excel Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!
Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help