PDA

View Full Version : Solved: VBA equivalent for Edit->Clear->All?



mleary2001
02-15-2006, 10:49 AM
Hi all,
Is there a vba equivalent in Office X that will clear all in a cell, contents and formats?

Thanks in advance,
Mike:banghead:

shades
02-15-2006, 12:33 PM
Howdy. Can you record that process, then post?

mleary2001
02-15-2006, 01:18 PM
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

shades
02-15-2006, 08:51 PM
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

mleary2001
02-16-2006, 09:02 AM
:thumbThanks! I just have to avoid the merged cells. It cannot clear the merged cells. I can live with that

-Mike

shades
02-16-2006, 10:39 AM
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.

geekgirlau
02-16-2006, 05:11 PM
If you want to remove everything (including merged cells) use

Selection.Clear