Consulting

Results 1 to 4 of 4

Thread: Clear Contents Leaving Formulae Intact

  1. #1
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location

    Clear Contents Leaving Formulae Intact

    Ok, here's the problem...

    I have the following procedure that I can easily change to simply "clear contents" so that all values and code are cleared while leaving formatting, conditional formatting, and data validation intact.

    It was suggested that it would be more useful if all entered "values" were cleared while leaving the s/s code intact also. I thought I had a simple solution but I was wrong...
    The procedure I now have below does the exact reverse of what is wanted....It removes the s/s code, leaving al the hand entered "values" intact.

    I've tried variations on the below to get the reverse - and also used a while (the cell value <> "=" clear the cell)/wend procedure - and if/then - and a copy/paste - but still no luck

    Any working solution would be appreciated


    Option Explicit
     
    Sub ClearDataBelowThisRow()
    Dim Question, StartClear As Integer, xlDataValidation As Validation
        Dim Password, Msg, Answer, Confirmation As VbMsgBoxResult
        Dim CellFormula, firstAddress
    On Error GoTo LastLine
        Question = InputBox("What is the last row number you want to KEEP?", _
        "Keep rows down to...")
        If Question <= 0 Then Exit Sub
        StartClear = Question + 1
        ActiveSheet.Unprotect
        Application.ScreenUpdating = False
        ActiveSheet.Rows(StartClear & ":65536").Select
    '//confirm
        Confirmation = MsgBox("Do you want to remove all borders, cell formatting," _
        & vbLf & "conditional formatting and data validation also?", _
        vbYesNoCancel, "Confirm Details...")
    '//if user chooses no, clear the cell contents only
         '<< NOTE: the following clears the s/s formulae but I want the opposite >>
         '<< i.e. to clear any 'contents' but leave the formulae intact >>
        If Confirmation = vbNo Then
            With Selection
                Set CellFormula = .Find("=", LookIn:=xlFormulas)
                If Not CellFormula Is Nothing Then
                    firstAddress = CellFormula.Address
                    Do
                        CellFormula.ClearContents
                        Set CellFormula = .FindNext(CellFormula)
                    Loop While Not CellFormula Is Nothing And _
                    CellFormula.Address <> firstAddress
                End If
            End With
            Range("A" & StartClear).Select
        Else
    '//if user chooses yes, delete all
            If Confirmation = vbYes Then
                Selection.Delete
                Range("A" & StartClear).Select
            Else
    '//if user decides to cancel
                If Confirmation = vbCancel Then
                    Range("A" & StartClear).Select
                End If
            End If
        End If
        Range("A" & StartClear).Select
        Exit Sub
    LastLine: '//an non-integer was entered for 'Question' so quit the sub
    End Sub

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    So you want to clear all inputted values but leave the formulas?

    Sheet1.Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents

  3. #3
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by DRJ
    So you want to clear all inputted values but leave the formulas?

    Sheet1.Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents
    Thanx jacob, that's exactly right, works all proper-like now.

    I originally had a "one-liner" solution but it was the WRONG "one-liner" and it ended up blowing out to the mish-mash above....

    John

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You're Welcome

    Take Care

Posting Permissions

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