Hi all,

Is the a way in with VBA code to remove ALL formats from a sheet, including
lines, width, text bold, etc., and set all back to default.

This would be very helpfull to me.

Thanks,
Ludovic

On Sun, 15 Nov 2009 16:31:21 +0100, "Vsn" <vsn_hotmail_kom wrote:

Hi all,

Is the a way in with VBA code to remove ALL formats from a sheet, including
lines, width, text bold, etc., and set all back to default.

This would be very helpfull to me.

Thanks,
Ludovic



Try this:

1) Insert a new worksheet into the workbook.
2) Select everything on the new worksheet (CTRL+A)
3) Copy everything from the new worksheet (CTRL+C)
4) Select everything on the worksheet you want to clean up (CTRL+A).
5) Paste Special and select Formats in the Paste section.

This will reset the formatting to the same as for a new worksheet.
You can now delete the worksheet from step 1.

Hope this helps / Lars-Åke

ActiveSheet.Cells.ClearFormats


"Vsn" <vsn_hotmail_kom wrote in message
...
Hi all,

Is the a way in with VBA code to remove ALL formats from a sheet,
including lines, width, text bold, etc., and set all back to default.

This would be very helpfull to me.

Thanks,
Ludovic

Assuming you want to set your fonts back to the default values as well, give
this a try...

With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With

--
Rick (MVP - Excel)


"Vsn" <vsn_hotmail_kom wrote in message
...
Hi all,

Is the a way in with VBA code to remove ALL formats from a sheet,
including lines, width, text bold, etc., and set all back to default.

This would be very helpfull to me.

Thanks,
Ludovic

On Sun, 15 Nov 2009 10:59:49 -0500, "Rick Rothstein"
wrote:

Assuming you want to set your fonts back to the default values as well, give
this a try...

With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With

Column Widths and Row Heights don't seem to be reset by .ClearFormats

Lars-Åke

I wasn't sure if the OP wanted Column Widths and Row Heights reset as well,
but I doing that that makes sense. Assuming the last row and last column
have not been changed, then I guess you could "steal" the ColumnWidths and
RowHeights values from them...

Rows.RowHeight = Rows(Rows.Count).RowHeight
Columns.ColumnWidth = Columns(Columns.Count).ColumnWidth

--
Rick (MVP - Excel)


"Lars-Åke Aspelin" wrote in message
...
On Sun, 15 Nov 2009 10:59:49 -0500, "Rick Rothstein"
wrote:

Assuming you want to set your fonts back to the default values as well,
give
this a try...

With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With

Column Widths and Row Heights don't seem to be reset by .ClearFormats

Lars-Åke

This works perfect, thanks a lot!

Ludovic

"Rick Rothstein" schreef in bericht
...
I wasn't sure if the OP wanted Column Widths and Row Heights reset as well,
but I doing that that makes sense. Assuming the last row and last column
have not been changed, then I guess you could "steal" the ColumnWidths and
RowHeights values from them...

Rows.RowHeight = Rows(Rows.Count).RowHeight
Columns.ColumnWidth = Columns(Columns.Count).ColumnWidth

--
Rick (MVP - Excel)


"Lars-Åke Aspelin" wrote in message
...
On Sun, 15 Nov 2009 10:59:49 -0500, "Rick Rothstein"
wrote:

Assuming you want to set your fonts back to the default values as well,
give
this a try...

With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With

Column Widths and Row Heights don't seem to be reset by .ClearFormats

Lars-Åke