Consulting

Results 1 to 7 of 7

Thread: Solved: VBA equivalent for Edit->Clear->All?

  1. #1
    VBAX Regular mleary2001's Avatar
    Joined
    Feb 2006
    Location
    Missoula
    Posts
    12
    Location

    Question Solved: VBA equivalent for Edit->Clear->All?

    Hi all,
    Is there a vba equivalent in Office X that will clear all in a cell, contents and formats?

    Thanks in advance,
    Mike

  2. #2
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Howdy. Can you record that process, then post?

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  3. #3
    VBAX Regular mleary2001's Avatar
    Joined
    Feb 2006
    Location
    Missoula
    Posts
    12
    Location

    Question

    Sure:

    When my user needs to recalculate data in a worksheet I first have to clear the old data and formatting from the worksheet before putting the new data in cells.

    In Excel we have:
    - Click on "Edit" ->Edit
    - then, in the resulting drop down menu, click on "Clear" ->Clear
    - in the resulting menu click on "All" ->All

    This clears both the contents of the selected cell, region, or worksheet.

    My question is: Can I do the same in VBA? I have looked at the Worksheets Collection, Worksheet Collection, searched help under Clear, Region, Format, Worksheet(s) to no avail.

    Any help much appreciated,
    Mike

  4. #4
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    This works in XL 2004 on OS X 10.3.9. Is this what you were asking?

    Sub Macro1()
        With Selection
            .ClearContents
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
    End Sub

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  5. #5
    VBAX Regular mleary2001's Avatar
    Joined
    Feb 2006
    Location
    Missoula
    Posts
    12
    Location

    Thumbs up That worked!

    Thanks! I just have to avoid the merged cells. It cannot clear the merged cells. I can live with that

    -Mike

  6. #6
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Merged cells are to be avoided at all costs. Use "Center Across Selection" - gives the same look, but allows Excel to use all its capabilities.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  7. #7
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    If you want to remove everything (including merged cells) use

    [VBA]Selection.Clear[/VBA]

Posting Permissions

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