Hi all,
Is there a vba equivalent in Office X that will clear all in a cell, contents and formats?
Thanks in advance,
Mike
Hi all,
Is there a vba equivalent in Office X that will clear all in a cell, contents and formats?
Thanks in advance,
Mike
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
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
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
Thanks! I just have to avoid the merged cells. It cannot clear the merged cells. I can live with that
-Mike
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
If you want to remove everything (including merged cells) use
[VBA]Selection.Clear[/VBA]