remove all format(s) on a sheet
 

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

remove all format(s) on a sheet
 

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

remove all format(s) on a sheet
 

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

remove all format(s) on a sheet
 

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

remove all format(s) on a sheet
 

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

remove all format(s) on a sheet
 

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

remove all format(s) on a sheet
 

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