remove all format(s) on a sheet

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

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
 
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
Click to expand...


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
 
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
 
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
Click to expand...

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
 
This works perfect, thanks a lot!

Ludovic

Rick Rothstein said:
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
Click to expand...
 
Back
Top