Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to clear data on all sheets

    I have a macro that clears data on all sheets , except the first and last sheet .

    The following are the exceptions :

    1) It must not clear text for Eg Oct 2013, Man etc
    2) Where a value begins with an equals sign for eg = 18000+50000-15000, this must also be cleared, except values to the right of Col A where text "GP % target" appears


    I have attached my sample data, which has the macro

    Your assistance in resolving this is most appreciated
    Attached Files Attached Files
    Last edited by HowardC; 2014-07-02 at 04:22.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Howard,

    See if this meets your needs:

    Code:
    Sub Clear_Data()
    On Error Resume Next
    '--------------------------------------------------------
    'DECLARE AND SET VARIABLES
    Dim r As Range
    Dim I As Integer
    '--------------------------------------------------------
    'CHECK CELLS IN USED RANGE AND CLEAR IF CRITERIA MET
    For I = 2 To Sheets.Count - 1
        For Each r In Sheets(I).UsedRange
            If IsNumeric(r) And r.HasFormula = False Or Cells(r.Row, 1) <> "GP % Target" And r.HasFormula Then
                r.ClearContents
            End If
        Next
    Next
    End Sub
    HTH,
    Maud

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for the help

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •